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

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:

1. Select an empty cell where you want to display the result.
2. Type the following formula:
   
   
   Formula:

   ---

   =SUMIFS(C:C,A:A,"="&DATE(2007,12,1),A:A,"<="&DATE(2008,1,31),B:B,"Rent") 


   ---

3. Press Enter.
   

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.

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

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

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