"AND" formula help?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A B C D E F G
=<3 4 - 6 7 - 8
1 Joe 2 Joe 2 0 0
2 Joe 3 Lee 1 2 0
3 Lee 5
4 Lee 5
5 Lee 1

I am looking for a formula in E, F and G that will count how many times Joe
and Lee are each under 3, between 4 and 6 and between 7 and 8, by reading
columns A and B. Thank you.
 
A B C D E F G
=<3 4 - 6 7 - 8
1 Joe 2 Joe 2 0 0
2 Joe 3 Lee 1 2 0
3 Lee 5
4 Lee 5
5 Lee 1
I am looking for a formula in E, F and G that will count how many times Joe
and Lee are each under 3, between 4 and 6 and between 7 and 8, by reading
columns A and B.
Click to expand...

Assuming that "Joe" and "Lee" are in D1 and D2 respectively (note: I
really think they should be in D2 and D3 if you want the titles "<=3",
"4-6" and "7-8"), try:

E1: =SUMPRODUCT(($A$1:$A$5=D1)*($B$1:$B$5<=3))
F1: =SUMPRODUCT(($A$1:$A$5=D1)*(4<=$B$1:$B$5)*($B$1:$B$5<=6))
G1: =SUMPRODUCT(($A$1:$A$5=D1)*(7<=$B$1:$B$5)*($B$1:$B$5<=8))

Copy E1:G1 to E2:G2.
 
Back
Top