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?

Countif does not work well over dis-joint ranges. Consider using SUMPRODUCT()
--
Gary's Student
gsnu200707


"MsBeverlee" wrote:

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"))


In article ,
MsBeverlee wrote:

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?

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

"MsBeverlee" wrote in message
...
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?

=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

"Gary''s Student" wrote:

Countif does not work well over dis-joint ranges. Consider using SUMPRODUCT()
--
Gary's Student
gsnu200707


"MsBeverlee" wrote:

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?

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

"Toppers" wrote:



=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

"Gary''s Student" wrote:

Countif does not work well over dis-joint ranges. Consider using SUMPRODUCT()
--
Gary's Student
gsnu200707


"MsBeverlee" wrote:

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?

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!

"Toppers" wrote:



=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

"Gary''s Student" wrote:

Countif does not work well over dis-joint ranges. Consider using SUMPRODUCT()
--
Gary's Student
gsnu200707


"MsBeverlee" wrote:

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?

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" wrote in message
...
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!

"Toppers" wrote:



=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

"Gary''s Student" wrote:

Countif does not work well over dis-joint ranges. Consider using
SUMPRODUCT()
--
Gary's Student
gsnu200707


"MsBeverlee" wrote:

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?

"MsBeverlee" wrote:
.. 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"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---