Excel 2003 - Sumif with 2 criteria 2

[colinmitton]

I have a spreadsheet which has sales data imported in. I'm trying to take these figures and give a break down of Year to Date and month to date (plus historical months).

The year to date was easy using the Sumif formula on the attached spreadsheet.

To help get the monthly figures I took the Month from the date and gave it a number so I could use it simply in a formula.

I'm now stuck as I need to do a sumif on two criteria, first by sales person then by month.

sales person	Inv Date	Inv Tot	        Month
101	        01/04/2004	£1,527.50	4
102	        01/04/2004	£785.00	        4
103	        01/04/2004	£188.00	        4
101	        01/05/2004	£156.86	        5
102	        01/05/2004	£559.30	        5
103	        01/05/2004	£1,527.50	5

so it should get some thing like :

sales person	YTD	  4	      5        
101	        £1684.36  £1,527.50   £156.86
102	        £1344.30  £785.00     £559.30        
103	        £1715.50  £188.00     £1,527.50

Any Ideas?

 

[AnotherHiggins]

Have a look at the SumProduct function.

If your data is on Sheet1 and your table with formulas is set up on Sheet2, then on Sheet2 in cell C2, you'd place this formula:
[tab][COLOR=blue white]=SumProduct((Sheet1!A2:A100 = A2) * (month(Sheet1!B2:B100) = C1) * (Sheet1!C2:C100))[/color]

Note: You don't need to break out the month in its own column if you don't want to - the above formula will find the month number of the date for you.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Oh, and I didn't use absolute references in that formula - if you want to be able to drag and fill the formula across columns and down rows, you'd need to use something like this:

=SumProduct((Sheet1!$A$2:$A$100 = $A2) * (month(Sheet1!$B$2:$B$100) = C$1) * (Sheet1!$C$2:$C$100))


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[colinmitton]

That Rocks....

Fantastic!

Thanks John your a life saver...

 

[GlennUK]

You might want to also experiment with PivotTables. Just an idea.

Cheers, Glenn.

Beauty is in the eye of the beerholder.