Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




SUMIFS formula using dates and text as criteria
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 
#2




Answer: SUMIFS formula using dates and text as criteria
To sum column C using two criteria  dates in column A and text "Rent" in column B, you can use the SUMIFS formula. Here are the steps to follow:
Explanation: The SUMIFS formula allows you to sum values based on multiple criteria. In this case, we want to sum values in column C based on two criteria:  Dates in column A that are greater than or equal to 12/1/2007 and less than or equal to 1/31/2008.  Text "Rent" in column B. The first argument of the formula (C:C) is the range of values that we want to sum. The second argument (A:A,"="&DATE(2007,12,1),A:A,"<="&DATE(2008,1,31)) specifies the criteria for dates in column A. We use the = and <= operators to include all dates between 12/1/2007 and 1/31/2008. The & symbol is used to concatenate the operators and the DATE function that creates a date value. The third argument (B:B,"Rent") specifies the criteria for text in column B. We simply use "Rent" as the text value. The formula will return the sum of all values in column C that meet both criteria. In this example, it will return $1,000.00.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.misc




SUMIFS formula using dates and text as criteria
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. "Trish" wrote: 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 
#4
Posted to microsoft.public.excel.misc




SUMIFS formula using dates and text as criteria
=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 "Trish" wrote in message ... 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 
#5
Posted to microsoft.public.excel.misc




SUMIFS formula using dates and text as criteria
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,"R ent") 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)  Biff Microsoft Excel MVP "Trish" wrote in message ... 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
sumifs will not work with a cell reference as criteria  Excel Discussion (Misc queries)  
sumifs formula in excel 2007  Excel Discussion (Misc queries)  
Excel 2007  SUMIFS formula use between tabs  Excel Discussion (Misc queries)  
SUMIFS Formula Problem  Excel Discussion (Misc queries)  
SUMIFS with dates  Excel Worksheet Functions 