Using the CountIf function with multiple criteria?

[Jonathan]

Hi all,

I'm trying to use the countif function to count the number of items that
have a specific name (column A), that has a "Yes Flag" for another field
(Column B)(marking only the items that have a Y), and then a certain "status"
(Column C).

I want the order of the function to go as follows: First, locate the trades
with the specific name (which I can do using just the countif function), then
check if these "flags" meet my criteria.

A problem I ran into is that there are certain fields that I don't want to
be used in the data pool I'm using, so my range can't be the entire field.
This is because there would be overlaps in the "name" field that I want to
use because these names come up in other fields as well.

Thanks.

 

[Pete_UK]

Try something like this:

=SUMPRODUCT((A1:A100="Y")*(B1:B100="name")*(C1:C100="x"))

where "x" is your status.

Adjust the range to suit your data (but they should all be the same).

Hope this helps.

Pete

 

[JP]

I don't understand what you mean in the last part, try this and see if
it works.

=SUMPRODUCT(($A:$A="specific name")*($B:$B="Y")*($C:$C="certain
status"))



HTH,
JP

 

[Jonathan]

Just for clarification, here is an example of what I want:

Name: Male: Brown: Size:

Apple Y Y Big
Dog Y Y Small
Dog N Y Small
Dog N Y Medium
Banana Y N Small
Cat N N Big
Apple Y N Big
Dog Y Y Small
Dog Y N Small
Cat Y N Medium

So, for example, I would want the count of the Small Dogs that are both Male
and Brown, which would be 2 in the above case using one formula pulling from
a large data pool with many more columns than these. Thanks!

 

[Gary''s Student]

Try SUMPRODUCT:

=SUMPRODUCT((A1:A100="mike")*(B1:B100="y")*(C1:C100="done"))

 

[Jonathan]

Getting a #NUM! error...please see my example posted above for more
clarification. Thanks!

 

[Pete_UK]

It's always better to post examples of your data. Try this:

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1000="Y")*(D11000="Small"))

This caters for up to 1000 rows, but adjust if you have more.

Hope this helps.

Pete

 

[Jonathan]

Hi Pete--thanks for the help.

Still not working (getting #NUM!)--here's the exact formula I put in with
the exception of the actual name:

=SUMPRODUCT(('Raw Data'!F:F="NAME")*('Raw Data'!CD:CD="Y")*('Raw
Data'!CI:CI="Y")*('Raw Data'!CL:CL="Dispatched"))

 

[Pete_UK]

If you are using XL2003 or earlier, you cannot use a full column
reference in SUMPRODUCT - you will have to make it something like
this:

=SUMPRODUCT(('Raw Data'!F2:F65536="NAME")*('Raw Data'!
CD2:CD65536="Y")*('Raw Data'!CI2:CI65536="Y")*('Raw Data'!
CL2:CL65536="Dispatched"))

This is looking at almost the full column (assuming you have a header
row), but this could take a long time to calculate if you have many of
them.

Hope this helps.

Pete

 

[Rick Rothstein \(MVP - VB\)]

Still not working (getting #NUM!)--here's the exact formula I put in with

the exception of the actual name:

=SUMPRODUCT(('Raw Data'!F:F="NAME")*('Raw Data'!CD:CD="Y")*('Raw
Data'!CI:CI="Y")*('Raw Data'!CL:CL="Dispatched"))

You can't use whole-column designations with SUMPRODUCT... you must specify a delineated range.

If you need the entire column to be covered, then try this instead...

=SUMPRODUCT(('Raw Data'!F1:F65535="NAME")*('Raw Data'!CD1:CD65535="Y")*('Raw Data'!CI1:CI65535="Y")*('Raw Data'!CL1:CL65535="Dispatched"))

although I think its more efficient to use a reduced range that is just large enough to cover the maximum row you ever intend to put data in.

Rick

 

[David Biddulph]

SUMPRODUCT will not accept a whole column. You can't use A:A, but you can
use A1:A65536

 

[Bob Phillips]

No you can't, that gets changed to A:A. Best you can do is A1:A65535

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)