Count multiple criteria

[KJ]

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.

 

[Pete_UK]

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:B100>0))

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

Hope this helps.

Pete

 

[Shane Devenshire]

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:B100>1))

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

 

[Pete_UK]

Shane,

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

Pete

 

[KJ]

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.

 

[KJ]

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[KJ]

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.