count if it meets a certain condition

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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"))
 
Upvote 0
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
 
Upvote 0
Thank you very much, both of you. I have soooooo much to learn about SumProduct.

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

Michael
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top