PC Pals Forum

Technical Help & Discussion => Windows PCs & Software: Help, News & Discussion => Topic started by: DJ on February 20, 2006, 15:27

Title: Excel - CountIf - Multiple Criteria
Post by: DJ on February 20, 2006, 15:27

Hi All,

Another excel questions  :laugh:

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

Title: Excel - CountIf - Multiple Criteria
Post by: sam on February 20, 2006, 22:06

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

Title: Excel - CountIf - Multiple Criteria
Post by: DJ on February 20, 2006, 22:38

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 :thumb:

DJ

Title: Excel - CountIf - Multiple Criteria
Post by: sam on February 21, 2006, 18:15

ummm

Title: Excel - CountIf - Multiple Criteria
Post by: DJ on February 21, 2006, 22:31

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 !  :blush:

DJ

Title: Excel - CountIf - Multiple Criteria
Post by: sam on February 21, 2006, 22:55

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

Title: Excel - CountIf - Multiple Criteria
Post by: TR on February 22, 2006, 17:38

Quote from: "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 :thumb:

DJ

DJ sorry I missed this one  :blush:

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

Title: Excel - CountIf - Multiple Criteria
Post by: DJ on February 22, 2006, 21:20

Yes - sorry forgot to update this.

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

Cheers :thumb:

DJ