# count if it meets a certain condition

#### daniels012

##### Well-known Member
I need to count the text "CEP"
if column E:E has "CEP" and Column F:F is January 2005.

What can I write to get that count?

Michael

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
=SUMPRODUCT(--(E2:E1000="CEP"),--(TEXT(F2:F1000,"mmmm yyyy") = "January 2005"))

Book1
EFGHI
1Field1Field2CEP1/1/2005
2CEP1/1/20051292
3XXX1/1/200599
4CEP1/1/200553
5CPE1/1/200576
6CEP2/1/200518
7CEP1/1/200527
8
9
10
Sheet1

Thank you very much, both of you. I have soooooo much to learn about SumProduct.

What do the "--" represent. Just trying to learn.

Michael

daniels012 said:
Thank you very much, both of you. I have soooooo much to learn about SumProduct.

What do the "--" represent. Just trying to learn.

Michael

The -- acts as a coercer.

The argument

(--(E2:E10=H1)

will product an array of TRUE/FALSE's. These need to be switched, or coerced, into their numeric counterparts in order for the SUMPRODUCT function to use them for multiplication.

-- does this, as would either of

((E2:E10=H1)+0)
((E2:E10=H1)*1)

Thank you

Michael

Replies
4
Views
475
Replies
9
Views
591
Replies
2
Views
232
Replies
0
Views
137
Replies
20
Views
354

1,217,323
Messages
6,135,899
Members
449,968
Latest member
Bpc1284

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