SUMIFS with multiple criteria between sheets

Hi Guys,

I'm trying to sum data based on a the criteria of a date being between a certain range and sorted by a particular name.

I have included a sample sheet with two tabs:

Totals-I would like to get the sum of the hours a person worked in the range of dates being 1/1/2014-1/24/2014 (which is D1 and E1 in the current charges sheet)
Current Charges-this is the page that I would like to gather the information from.


Below is a sample of the formula I was trying to use, but I get an error....my interpretation of what I was trying to input is as follows: Sum C2:C34 in the current charge sheet if B2:B34 is greater than or equal to D1 on the current charges tab (which is 1/1/2014) and B2:B34 is less than or equal to E1 (which is 1/24/2014) with A4:A34 in the current charges sheet equal to A2 on Totals sheet.

SUMIFS('Current Charges'!C2:C34,'Current Charges'!$B$2:$B$34,">="&'Current Charges'!$D$1,Current Charges'!$B$2:$B$34,"<="&'Current Charges'!$E$1,'Current Charges'!$A$4:$A$34,A2)


Test Data:

SUMIFS Test Data.xlsx

Hi Hemesh,

Looks like your first formula worked the best for me, so I will try it in the original sheet and see what happens.

For some reason I couldn't get your second formula to do quite what I wanted it to do in the Test Data

Thanks for your help!

Ok, not quite sure where you going with this, maybe elaborate? I have my range from $A$4:$A$34 .......I just wanted that range to match A2 as one of the two criteria. I am not an expert in this though, so I'm sure you are seeing something that I am not seeing.

Thanks for your response

Originally Posted by Tony Valko

I didn't look at your file...

All the ranges need to be the same size:

'Current Charges'!C2:C34
'Current Charges'!$B$2:$B$34
'Current Charges'!$A$4:$A$34

Ah, got it, thanks.

I fixed that, but still had an error with that formula...and the one that I used from the guy above doesn't seem to work either....I switched it up though, so it is probably an error in how I am interpreting the formula.

=SUMPRODUCT((YEAR('Current Charges'!F4:F1023)='Total HH PHS 3'!E6)*('Current Charges'!D4:D1023='Total HH PHS 3'!A8)*('Current Charges'!G4:G1023))


Sheet
Total HH PHS 3=Totals

compared with his formula

=SUMPRODUCT((YEAR('Current Charges'!$B$2:$B$34)=Totals!B$1)*('Current Charges'!$A$2:$A$34=Totals!$A2)*('Current Charges'!$C$2:$C$34))

I'm not seeing what I am doing wrong....can you see anything?

Well, neither, it's just coming back blank, when I know that there are values that sum up to the amount....no error messages, just a blank square

It seems to be working for the most part in my sample.....what are other common errors people tend to make?

My formula for actual
=SUMPRODUCT((YEAR('Current Charges'!$F$4:$F$1021)=E$6)*('Current Charges'!$D$4:$D$1021=$A8)*('Current Charges'!$G$4:$G$1021))



I made the sample a bit more realistic....it seems to be working okay

=SUMPRODUCT((YEAR('Current Charges'!$B$4:$B$244)=D$4)*('Current Charges'!$A$4:$A$244=$A5)*('Current Charges'!$C$4:$C$244))

Yes, I want it to first sort through the column and find D4 and the in the 2014 column (E4) find dates that fall within that range as well....it actually seems to be working on the sample, but not so much on the real thing....I actually don't know if there is a common error I am making, or what.

thanks for your response.

Thanks guys, all of these responses seem like they should work-seeing as though they worked in the sample data that had the same criteria.

Not sure what the issue is, but I will consider this a done deal for now. Have a great week!

Ha! I found out what the problem was! In my date, I used '2014 instead of changing it to a number with zero decimals...I feel like an idiot, but hopefully this can help someone else in the future!


Thanks for all of you guys time, come to Colorado and a beer is on me!