How do you create COUNTIF functions based on multiple criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a COUNTIF function in 1 worksheet based on multiple
criteria in another worksheet. Basically, I want to count the number of
cells that the criteria of 3 different columns in a separate worksheet (i.e.
$A:$A="Smith" and $AS:$AS="4/19/2007" and $CL:$CL="$419"). I want to count
the number of cells that meet all 3 of these criteria. Can it be done?
 
One way:

=SUMPRODUCT(--(Sheet2!$A$1:$A$65535 = "Smith"),
--(Sheet2!$AS$1:$AS$65535 = DATE(2007,4,19), --(Sheet2!$CL$1:$CL$65535 =
"$419"))
 
You need a different function. Try this:

=SUMPRODUCT(--(A1:A10="Smith"),--(AS1:AS10=DATE(2007,4,19)),--(CL1:CL10=419))

Note: you can't use entire columns as references with sumproduct in Excel
versions prior to Excel 2007)

I'm assuming $419 is a numerical value.

It's better to use cells to hold the criteria:

D1 = Smith
D2 = 4/19/2007
D3 = 419

=SUMPRODUCT(--(A1:A10=D1),--(AS1:AS10=D2),--(CL1:CL10=D3))

Biff
 
=SUMPRODUCT(--(Sheet2!A1:A10="Smith"),--(Sheet2!AS1:AS10=--"2007-04-19"),--(Sheet2!CL1:CL10=419))

SUMPRODUCT can use full columns i.e A:A, so define range as required e.g
A1:A10000

HTH
 
..... SUMPRODUCT can should have been can not!

Toppers said:
=SUMPRODUCT(--(Sheet2!A1:A10="Smith"),--(Sheet2!AS1:AS10=--"2007-04-19"),--(Sheet2!CL1:CL10=419))

SUMPRODUCT can use full columns i.e A:A, so define range as required e.g
A1:A10000

HTH
Click to expand...
 
Thank you so much! This totally worked. Thank you for your help.

One other question you can maybe help me with. With this same function, is
it possible to count the number of cells that fall between 2 dates?
Specifically, can I use this same function to count how many cells meet the
criteria of "check-in date>=4/19/2007" and "check-out date<=4/26/2007"?

Thanks!
 
Try one of these:

C1 = 4/19/2007
D1 = 4/26/2007

=SUMPRODUCT(--(A1:A20>=C1),--(A1:A20<=D1))

This method comes from Ron Coderre.

=INDEX(FREQUENCY(A1:A20,C1:D1-{1,0}),2)

This method is more efficient especially if the range is large.

Biff
 
MsBeverlee said:
.. count how many cells meet the
criteria of "check-in date>=4/19/2007"
and "check-out date<=4/26/2007"?
Click to expand...

Frame it up like this:
=SUMPRODUCT((Sheet2!A1:A100>= --"19 Apr 2007")*(Sheet2!A1:A100<= --"26 Apr
2007"))

---
 
Back
Top