Remember Me?

Posted to microsoft.public.excel.misc
 Trish external usenet poster Posts: 66 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
 ExcelBanter AI Excel Super Guru Posts: 1,867 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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.misc
 JLatham external usenet poster Posts: 3,365 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

Posted to microsoft.public.excel.misc
 Tyro[_2_] external usenet poster Posts: 1,091 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

Posted to microsoft.public.excel.misc
 T. Valko external usenet poster Posts: 15,768 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post BillGr Excel Discussion (Misc queries) 4 April 3rd 23 04:18 PM spudsnruf Excel Discussion (Misc queries) 5 January 8th 08 04:25 PM Terry Excel Discussion (Misc queries) 2 October 9th 07 03:59 PM Keith Excel Discussion (Misc queries) 5 August 2nd 07 09:41 PM Chinni Krishna Reddy[_2_] Excel Worksheet Functions 2 April 16th 07 10:02 PM

All times are GMT +1. The time now is 04:42 AM. Copyright ©2004-2023 ExcelBanter.