Sumnproduct formula

[Rapesco]

Hello, Ive got a spreadsheet with around 100 columns in it. but i need a formula for 2 columns. one column has a number 1,2,3,4, or 5 in it. the other column has different numbers it.

I'm wanting to have a formula that looks at the first column (with 1,2,3,4, or 5 in it. For example it it sees "1" in the first column & will count the amount of numbers in the other column that are under 10. Does that make sense? For example:

1 100
1 5
3 50
1 8
4 60
5 2

So i would need it to return the number "2"

Thanks

[BigBas]

It sounds doable, but we need a bit more detail. How does column A affect the count of Column B. (Should we assume that if there is a 2 in column B, we count numbers less than 20)?

[Rapesco]

Thanks for the reply, column A determines if it should look at column b. I want it to look at all the cells in col A with 1 in. If they have a 1 in count all cells in col b that are under 10.

so it needs some sort of =IF function then a countif formula in the same cell but dont know how to do it.

[BigBas]

Try this and let me know if it works:

=SUMPRODUCT(--(A1:A6=1),--(B1:B6<10))

[Rapesco]

That worked, thanks very much for your help.

[BigBas]

Glad I could help.