Sumifs

[brycez28]

I am creating a report that uses data imported from a SQL Database. Sheet 1 (Data) contains the imported Data:
ColA- SalesID
ColB- CustAccount
ColC-SalesName
ColD-OrderReceivedMonth
ColE-OrderReceievedYear
ColJ-LineAmount

I then have another sheet (BookingsReport) that contains:
A5-SalesName
B5-CustAccount B2=(Manually entered year) [2011 for instance]
D5-CustGroup
E5-April E4=B2
F5-May F4=B2
G5-June G4=B2
H5-July H4=B2
I5-Aug I4=B2
J5-Sept J4=B2
K5-Oct K4=B2
L5-Nov L4=B2
M5-Dec M4=B2
N5-Jan N4=B2+1
O5-Feb O4=B2+1
P5-March P4=B2+1



For each month, I need to formulate the sum of all orderes (LineAmount) by the CustAccount. I have the formula in E6 to E2000
=SUMIFS(Data!$J$5:$J$200000,Data!$B$5:$B$200000,BookingsReport!$B6,Data!$D5:$D200000,MONTH(E$5))
This formula successfully sums the numbers for each CustAccount for each month. The problem I'm having is getting it to sort our by year as well. I've tried the formula:
=SUMIFS(Data!$J$5:$J$200000,Data!$B$5:$B$200000,BookingsReport!$B6,Data!$D5:$D200000,MONTH(E$5), Data!$E$5:$E$200000, YEAR(E$4))
But all that I get back is "0" for everything. What am I doing wrong? Any help would be greatly appreciated!

[NBVC]

are you sure that E5:E200000 has dates, or is it years only?

[brycez28]

E5:E200000 has years only.

[NBVC]

Then you don't need YEAR(E$4), just use E$4 as your criteria.

[brycez28]

That works! Thank you!

[NBVC]

You should double-check the month column D. If those are just month numbers, then you shouldn't need MONTH(E$5) as a criteria...

[brycez28]

I stand corrected...Column D is just months, but E5:P5 are dates (4/1/2011, 5/1/2011, etc) with just the month name showing.

[NBVC]

I am assuming you are good to go now?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save