Sumif on match AND date criteria?

[Ajw43022]

I need to sum values in a column if both serial numbers match AND if the match's date is less than 5 days ago.

I can do both individually:

=SUMIF('All2'!$AH$3:$AH$1000,F4,'All2'!$AI$3:$AI$1000)

=SUMIF('All2'!$AJ$3:$AJ$1095,"<="&NOW()-5,'All2'!$AI$3:$AI$1095)

But I can't for the life of me figure out how to combine the two. Any help would be very appreciated!

 

[Sandeep Warrier]

I need to sum values in a column if both serial numbers match AND if the match's date is less than 5 days ago.

I can do both individually:

=SUMIF('All2'!$AH$3:$AH$1000,F4,'All2'!$AI$3:$AI$1000)

=SUMIF('All2'!$AJ$3:$AJ$1095,"<="&NOW()-5,'All2'!$AI$3:$AI$1095)

But I can't for the life of me figure out how to combine the two. Any help would be very appreciated!

Hi,

How about

=SUMPRODUCT(--('All2'!$AH$3:$AH$1000=F4),--('All2'!$AJ$3:$AJ$1000<=Now()-5),'All2'!$AI$3:$AI$1000)

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Ajw43022]

Thanks for your response!

I'm getting an #N/A when I apply that unfortunately though. No idea why. The two equations separately work like a charm, and yours seems like a logical combination of the two, but somehow there's a problem.

Any ideas?

thank you.

 

[Sandeep Warrier]

Using SUMPRODUCT in this manner requires ranges to be the same size.

What is the data in 'All2'!$AH$3:$AH$1000? Text, Numbers...?

Try

=SUMPRODUCT(('All2'!$AH$3:$AH$1000=F4)*('All2'!$AJ$3:$AJ$1000<=Now()-5)*('All2'!$AI$3:$AI$1000))

OR

=SUM(IF('All2'!$AH$3:$AH$1000=F4,IF('All2'!$AJ$3:$AJ$1000<=Now()-5,'All2'!$AI$3:$AI$1000,0)))

Confirmed with Control+Shift+Enter and not just Enter.

Also, I'm not sure if it'll help, but try TODAY()-5 instead of NOW()-5

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Ajw43022]

Found the problem! Rows 800+ for AH were #VALUE!. This didn't affect the formulas individually, but they messed up the SUMPRODUCT. I corrected that, and now your original formula works wonderfully.

Thank you so much, and I apologize for the screw-up on my end.

 

[swayp11]

If you are in Excel 2007, you can use the SUMIFS formula. It will sum one range if any multiple number of criteria are matched.

 

[Sandeep Warrier]

Found the problem! Rows 800+ for AH were #VALUE!. This didn't affect the formulas individually, but they messed up the SUMPRODUCT. I corrected that, and now your original formula works wonderfully.

Thank you so much, and I apologize for the screw-up on my end.

You're welcome. I'm glad you were able to resolve the problem.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">