# Easy formula Question...counting

#### thepartydj

##### Active Member
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!

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

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

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

Where C1=boy and C2=3

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.

You could do it with:

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

Richard

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

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

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))

Replies
1
Views
573
Replies
3
Views
322
Replies
9
Views
510
Replies
3
Views
161
Replies
36
Views
1K

1,217,320
Messages
6,135,870
Members
449,966
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back