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.