Easy formula Question...counting

[thepartydj]

I would like to get the total count of something only if both columns match.

Example

Column one Column Two
Boy 3
Girl 4
Boy 3
Boy 3
Girl 3
Boy 4

SO if I wanted how many boys are three (3 of them) what would be the formula? Thanks!

 

[Richard Schollar]

Hi

I think SUMPRODUCT is what you need:

=SUMPRODUCT(--(A1:A10="Boy"),--(B1:B10=3))

the criteria can be range references holding the values and obviously amned the ranges in the Sumproduct to suit.

Richard

 

[Datsmart]

=SUMPRODUCT(--(A2:A7="boy"),--(B2:B7=3),--(B2:B7<>""))

 

[just_jon]

=SUMPODUCT(--(A1:A10=C1),--(B1:B10=C2))

Where C1=boy and C2=3

 

[thepartydj]

add on to this code.

In column one I might have boy followed by a name in the same cell. How can I add so it only looks at the first word and not the other words in the cell? I tried "boy*" but that doesn't work.

 

[Richard Schollar]

You could do it with:

=SUMPRODUCT(--(LEFT(A1:A10,3)="boy"),--(B1:B1-=3))

Richard

 

[thepartydj]

Actually that code doesn't read everything behind boy just like it does with just boy. any other sugesstions
also
How about instead of doing 3 i write it out like "three" what changes in the code because I am trying to put qotes around it like boy but it isn't working. Thanks

 

[thepartydj]

I just split out the columns and then it worked. Thanks

 

[just_jon]

If you want to have a variable-length target -- Boy or Girl in cell C1, for example, an the year in C2, then

=SUMPODUCT(--(LEFT(A1:A10,LEN(C1))=C1),--(B1:B10=C2))