SUMIFS formula using dates and text as criteria

  • Thread starter Thread starter Trish
  • Start date Start date
T

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
 
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.
 
=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
 
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)
 
Back
Top