Topic: Excel - CountIf - Multiple Criteria

[DJ]

Hi All,

Another excel questions  

I have one sheet that is in the format of (basically);

Month     Cust    Invoice No     Fees     Exp


Months are Jan, Feb etc. Cust is a choice of 5.

I want (on another sheet) to count the number of invoices done for each customer on a certain month.

So I thought I could do a coutif function, but unsure how to use it for multiple criteria.

Any ideas?

Thanks

DJ

[sam]

ummm im unsure, ill have a think for ya tomorrow when my mind is actually awake.

[DJ]

Think I've got it

Code: [Select]

{=SUM(('Sheet1'!$C$5:$C$600=CustA)*('Sheet1'!$U$5:$U$600=June))}


Now another one!!....

How do I change this so that it Sums a certain column, if the Cust is CustA, Month is June and the values I to sum are in E5:E600

Cheers

DJ

[sam]

ummm

[DJ]

Don't worry - think I'm getting somewhere - slowly....  will post back soon.

I have this habit of posting a question then working out the answer myself !  

DJ

[sam]

oh no thats a good thing.... and if you dont sort it by tomorrow i'm sure my mind will have a moment of inspiration.... i could write you an idl script that would do it in no time, but thats useless to u

[TR]

Think I've got it

Code: [Select]

{=SUM(('Sheet1'!$C$5:$C$600=CustA)*('Sheet1'!$U$5:$U$600=June))}


Now another one!!....

How do I change this so that it Sums a certain column, if the Cust is CustA, Month is June and the values I to sum are in E5:E600

Cheers

DJ

DJ sorry I missed this one  

I have tyried this out on my spread sheets and it works with mine but dont know if it does on yours?

{=SUM(('Sheet1'!$C$5:$C$600=CustA)*('Sheet1'!$U$5:$U$600=June))}($u$5:$u$600)


tr

[DJ]

Yes - sorry forgot to update this.

Everything is working. Forgot to CTRL + SHIFT + Enter to make the array formula work.

Cheers

DJ