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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
=SUMPRODUCT(--(E2:E1000="CEP"),--(TEXT(F2:F1000,"mmmm yyyy") = "January 2005"))
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Thank you very much, both of you. I have soooooo much to learn about SumProduct.

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

Michael
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,554
Members
412,537
Latest member
Mohamed_5966
Top