Count multiple criteria
 

I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year.

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down.

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.

Count multiple criteria
 

You can only use SUMIF and COUNTIF when you have only one condition.
For more conditions you need to use SUMPRODUCT (and you can use this
for summing and counting). Try this:

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000))

Adjust the ranges to suit, but you can't use full-column references
unless you have XL2007.

Hope this helps.

Pete

On Dec 2, 3:44*pm, KJ wrote:
I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year. *

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. *I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down.

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.

Count multiple criteria
 

Hi,

If you are using 2007

COUNTIFS(A1:A100,D1,B1:B100,"1")

where you enter the date you want to check for in D1, any format.

In 2003

=SUMPRODUCT(--(A1:A100=D1),--(B1:B1001))

again the date you want to check for is in D1 as any legitimate Excel date.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"KJ" wrote:

I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year.

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down.

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.

Count multiple criteria
 

Shane,

the OP indicated that s/he wanted counts for a particular month, not a
specific date.

Pete

On Dec 2, 5:09*pm, Shane Devenshire
wrote:
Hi,

If you are using 2007

COUNTIFS(A1:A100,D1,B1:B100,"1")

where you enter the date you want to check for in D1, any format.

In 2003

=SUMPRODUCT(--(A1:A100=D1),--(B1:B1001))

again the date you want to check for is in D1 as any legitimate Excel date.

If this helps, please click the Yes button

Cheers,
Shane Devenshire



"KJ" wrote:
I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year. *

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. *I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down..

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.- Hide quoted text -

- Show quoted text -

Count multiple criteria
 

Thanks for that. Seems to work a treat on my sample. Will have a go on the
real thing tomorrow. Didn't have the TEXT bit or mmm-yy at all - simply went
into range, "Jan-08". Also, hadn't realised I couldn't use full column
references in v2003.

Thank you kindly.


"Pete_UK" wrote:

You can only use SUMIF and COUNTIF when you have only one condition.
For more conditions you need to use SUMPRODUCT (and you can use this
for summing and counting). Try this:

=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B1000))

Adjust the ranges to suit, but you can't use full-column references
unless you have XL2007.

Hope this helps.

Pete

On Dec 2, 3:44 pm, KJ wrote:
I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year.

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down.

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.

Count multiple criteria
 

Thanks for the reply. I'm using v2003. Can you confirm what the -- is. Is
this the sheet ref or something else (excuse my ignorance). Also, does the
date I put in need to be in " "?

"Shane Devenshire" wrote:

Hi,

If you are using 2007

COUNTIFS(A1:A100,D1,B1:B100,"1")

where you enter the date you want to check for in D1, any format.

In 2003

=SUMPRODUCT(--(A1:A100=D1),--(B1:B1001))

again the date you want to check for is in D1 as any legitimate Excel date.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"KJ" wrote:

I'm trying to count how many times a combination of 2 criteria occur at the
same time across a range that sits in another worksheet in the same workbook
so I can summarise the amount of sales (not value) by month & year.

I've spent ages looking at the advice on this so far and tried various
suggestions involving the sumproduct, countif and IF functions. I can manage
the countif and sumif when there is just 1 criteria but when I add in the
second one, I get 0 returned each time or with the sum and countif together,
it adds both countifs up.

Here's what I'm trying to do if anyone can help.
Col A contains month and year and is custom formatted as mmm-yy eg Jan-08
Col B contains a numerical value eg £100
Col C - I've set up as a count column so it contains 1 all the way down.

I'd like to know how many occurances of say Jan-08 in Col A also have a
value (any number 1) showing in Col B.

Thanks.

Count multiple criteria
 

You're welcome - thanks for feeding back.

Pete

On Dec 2, 11:03*pm, KJ wrote:
Thanks for that. *Seems to work a treat on my sample. *Will have a go on the
real thing tomorrow. *Didn't have the TEXT bit or mmm-yy at all - simply went
into range, "Jan-08". *Also, hadn't realised I couldn't use full column
references in v2003.

Thank you kindly.

Count multiple criteria
 

Hi Pete,
I've now applied your function to my master sheet and it works great.
However, I've met two further problems.
1. By using the 0, it then doesn't include any 0 entries I might have in
Col B (Sales Value). I changed the formula to =0 at the end but it then
included the cells that were blank too (cells may be blank until I've agreed
a sale price).
=SUMPRODUCT((TEXT(A1:A100,"mmm-yy")="Jan-08")*(B1:B100=0))
Is there a way of setting the 2nd criteria to be a value or entry of any
kind eg wildcard*.

2. I also need to calculate the value of all the sales when both my criteria
are met. eg. sum if Col A is Jan-08 and Col B has an entry of any value,
what is the sum of the values in Col B. Have tried amending the sumproduct
function but can't quite get there.

Thanks for any help you can offer.

"Pete_UK" wrote:

You're welcome - thanks for feeding back.

Pete

On Dec 2, 11:03 pm, KJ wrote:
Thanks for that. Seems to work a treat on my sample. Will have a go on the
real thing tomorrow. Didn't have the TEXT bit or mmm-yy at all - simply went
into range, "Jan-08". Also, hadn't realised I couldn't use full column
references in v2003.

Thank you kindly.