SUMIFS formula using dates and text as criteria

[Trish]

I am trying to sum column C using two criteria - dates, column A and text
"Rent" in Column B. Nothing I have tried works with the dates. Any
suggestions.

example

A B C D
12/1/07 Rent $500.00
12/31/07 Misc $100.00
1/1/08 Rent $500.00
1/10/08 Misc $100.00

 

[JLatham]

Could you gives us an example or two of how you've been trying to use the
SUMIFS() function? Maybe we can spot what's not right.

 

[Tyro]

=SUMIFS(C1:C4,A1:A4,DATEVALUE("1/1/2008"),B1:B4,"rent") or
=SUMIFS(C1:C4,A1:A4,"="&DATEVALUE("1/1/2008"),B1:B4,"=rent")
or even =SUMIFS(C1:C4,A1:A4,"="&DATEVALUE("1/1/2008"),B1:B4,"="&"rent")

Excel defaults to a comparison for equality unless otherwise specified.
Thats why you can leave out the equal sign as in the first formula.
Column C is summed if the date in column A is 1/1/2008 and the corresponding
entry in column B is Rent. You can also put the date in a cell like E1 and
substitute E1 for the DATEVALUE function and put Rent in, say, F1 and put F1
instead of "rent" in the formula.

Tyro

 

[T. Valko]

You didn't say what your date criteria was. This will sum where dates are
for December 2007:

Excel 2007 using SUMIFS:

=SUMIFS(C2:C5,A2:A5,">="&DATE(2007,12,1),A2:A5,"<="&DATE(2007,12,31),B2:B5,"Rent")

Or, using cells to hold the criteria:

F1 = 12/1/2007
G1 = 12/31/2007
H1 = Rent

=SUMIFS(C2:C5,A2:A5,">="&F1,A2:A5,"<="&G1,B2:B5,"Rent")

Other versions of Excel (including Excel 2007):

=SUMPRODUCT(--(TEXT(A2:A5,"m/yyyy")="12/2007"),--(B2:B5="Rent"),C2:C5)

Or, using cells to hold the criteria:

=SUMPRODUCT(--(A2:A5>=F1),--(A2:A5<=G1),--(B2:B5=H1),C2:C5)