Using the CountIf function with multiple criteria?
 

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.

Using the CountIf function with multiple criteria?
 

Try something like this:

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

where "x" is your status.

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

Hope this helps.

Pete

On Jan 9, 5:06*pm, Jonathan
wrote:
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.

Using the CountIf function with multiple criteria?
 

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

On Jan 9, 12:06*pm, Jonathan
wrote:
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.

Using the CountIf function with multiple criteria?
 

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!

"Jonathan" wrote:

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.

Using the CountIf function with multiple criteria?
 

Try SUMPRODUCT:

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

--
Gary''s Student - gsnu200764


"Jonathan" wrote:

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.

Using the CountIf function with multiple criteria?
 

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

"Gary''s Student" wrote:

Try SUMPRODUCT:

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

--
Gary''s Student - gsnu200764


"Jonathan" wrote:

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.

Using the CountIf function with multiple criteria?
 

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

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1 000="Y")*(D1:D1000="Small"))

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

Hope this helps.

Pete

On Jan 9, 5:22*pm, Jonathan
wrote:
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!



"Jonathan" wrote:
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.- Hide quoted text -

- Show quoted text -

Using the CountIf function with multiple criteria?
 

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" wrote:

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

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1 000="Y")*(D1:D1000="Small"))

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

Hope this helps.

Pete

On Jan 9, 5:22 pm, Jonathan
wrote:
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!



"Jonathan" wrote:
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.- Hide quoted text -

- Show quoted text -

Using the CountIf function with multiple criteria?
 

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

On Jan 9, 5:50*pm, Jonathan
wrote:
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" wrote:
It's always better to post examples of your data. Try this:

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1 000="Y")*(D1:D1000="Small*"))

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

Hope this helps.

Pete

On Jan 9, 5:22 pm, Jonathan
wrote:
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!

"Jonathan" wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Using the CountIf function with multiple criteria?
 

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

Using the CountIf function with multiple criteria?
 

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

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

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

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1 000="Y")*(D1:D1000="Small"))

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

Hope this helps.

Pete

On Jan 9, 5:22 pm, Jonathan
wrote:
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!



"Jonathan" wrote:
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.- Hide quoted text -

- Show quoted text -

Using the CountIf function with multiple criteria?
 

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)



"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
SUMPRODUCT will not accept a whole column. You can't use A:A, but you can
use A1:A65536
--
David Biddulph

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

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

=SUMPRODUCT((A1:A1000="Dog")*(B1:B1000="Y")*(C1:C1 000="Y")*(D1:D1000="Small"))

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

Hope this helps.

Pete

On Jan 9, 5:22 pm, Jonathan
wrote:
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!



"Jonathan" wrote:
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.- Hide quoted text -

- Show quoted text -