# Easy formula Question...counting

#### thepartydj

##### Board Regular
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Richard Schollar

##### MrExcel MVP
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

##### Well-known Member
Code:
=SUMPRODUCT(--(A2:A7="boy"),--(B2:B7=3),--(B2:B7<>""))

#### just_jon

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

Where C1=boy and C2=3

#### thepartydj

##### Board Regular

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

##### MrExcel MVP
You could do it with:

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

Richard

#### thepartydj

##### Board Regular

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

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

#### just_jon

##### Legend
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
4
Views
160
Replies
8
Views
174
Replies
9
Views
144
Replies
6
Views
188
Replies
0
Views
105

1,141,707
Messages
5,707,979
Members
421,539
Latest member
zuniBM

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