How do you create COUNTIF functions based on multiple criteria?

[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?

 

[Guest]

Countif does not work well over dis-joint ranges. Consider using SUMPRODUCT()

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(Sheet2!$A$1:$A$65535 = "Smith"),
--(Sheet2!$AS$1:$AS$65535 = DATE(2007,4,19), --(Sheet2!$CL$1:$CL$65535 =
"$419"))

 

[T. Valko]

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

 

[Guest]

=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

 

[Guest]

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

=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

 

[Guest]

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!

 

[T. Valko]

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,C11-{1,0}),2)

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

Biff

 

[Max]

.. count how many cells meet the
criteria of "check-in date>=4/19/2007"
and "check-out date<=4/26/2007"?

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

---