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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
=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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,018
Messages
5,834,974
Members
430,331
Latest member
Syed Yasir Hannan

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
Top