PDA

View Full Version : Solved: Using SumIf with a Date Range as the Criteria



Opv
02-20-2010, 06:50 PM
I am attempting to incorporate the SUMIF function into a spreadsheet to extract total rent paid for a particular month and year. My data sheet has a column named Date and a column named RentPaid, as well as a cell named ReportMonth.

What I envision is defining the month and year for which I want to perform the calculation in the cell named ReportMonth, then to have the SUMIF formula read through the rows of data and extract amounts from RentPaid for the dates that fall within the designated month and year within the Date column.

My current formula is =SUMIF(Date,"="&ReportMonth,RentPaid)

This formula results in a 0.

How would I reconstruct the formula to achieve the desired result?

Thanks,

Opv

Opv
02-20-2010, 07:19 PM
Actually, "ReportMonth" is misleading. It is actually a field that reflects the MONTH and YEAR for which the data should be extracted, i.e., Jan 2010.

mbarron
02-20-2010, 08:03 PM
Please provide a sample of your workbook. It will eliminate the necessity numerous questions.
Like.
Is ReportMonth a date field?

Opv
02-20-2010, 08:15 PM
A sample of the file is attached. The field in question is a date field.

mbarron
02-20-2010, 08:31 PM
You can use something like this:

=SUMPRODUCT(--(MONTH(C6:C12)=MONTH(G6)),(--YEAR(C6:C12)=YEAR(G6))*E6:E12)

You cannot use your Date named range because it contains text values within it as well as date values.

Opv
02-20-2010, 08:39 PM
Thanks, mbarron. Actually, I think I edited my original post while you were preparing the solution. I edited your formula to include the cell reference to the RentPaid column rather than the RentDue column (which was erroneously included in my original formula). It works as intended.

Thanks again for your help!

Opv

Bob Phillips
02-21-2010, 03:10 AM
You can use something like this:

=SUMPRODUCT(--(MONTH(C6:C12)=MONTH(G6)),(--YEAR(C6:C12)=YEAR(G6))*E6:E12)
You cannot use your Date named range because it contains text values within it as well as date values.
There is absolutely no need to mix double unaries and multiplication opertaors, nor wrap the year test in brackets

=SUMPRODUCT(--(MONTH(C6:C12)=MONTH(G6)),--(YEAR(C6:C12)=YEAR(G6)),E6:E12)

Opv
02-23-2010, 08:53 AM
The SUMPRODUCT formula is working as desired as it relates to my original issue. I wasn't aware when I marked this thread "SOLVED" that there would be a related issue pop up.

If any of the cells in the designated ranges are populated (or blank) as a result of a formula, i.e., =IF(Date="","",defined action), these blank cells are causing the SUMPRODUCT formula to result in a #VALUE! error.

I include the IF formula so that I can control how many future rows of pending data are actually displayed. My preference would be to retain the IF formulas in the designated ranges.

Is there a way to incorporate the ISNUMBER function to this formula so that it operates only on the cells within the designated ranges that contain a numeric value?

=SUMPRODUCT(--(MONTH(C6:C12)=MONTH(G6)),--(YEAR(C6:C12)=YEAR(G6)),E6:E12)

mbarron
02-23-2010, 11:02 AM
Can you post a sample that has the error?

Opv
02-23-2010, 11:27 AM
OK, I think I have a sample attached.

Thanks

mbarron
02-23-2010, 12:26 PM
You don't need to check for a number in the range of values. The reason for the #VALUE errors were:

1) The Current Month Interest formula was looking at a date field (E1) for the month comparison and a blank cell (F1) for the year comparison.

2)The YTD interest had a SUMPRODUCT result being multiplied by a range.

Formulas in F23 and F24 were:
F23: =SUMPRODUCT(--(MONTH(C5:C22)=MONTH($E$1)),(--YEAR(C5:C22)=YEAR($F$1))*$F5:F22)
F24: =SUMPRODUCT((YEAR(C5:C22))=YEAR($E$1))*F5:F22

Change them to:
F23: =SUMPRODUCT(--(MONTH(C5:C22)=MONTH($E$1)),--(YEAR(C5:C22)=YEAR($E$1)),$F5:F22)
F24: =SUMPRODUCT(--(YEAR(C5:C22)=YEAR($E$1)),F5:F22)

Opv
02-23-2010, 01:08 PM
Thanks, mbarron. That did the trick!