Adding values from different columns from specific date ranges

[InterstateRentals]

We have a variety of payments that we receive during a given month. I'd like to be able to collect all payments that are entered in multiple columns within a specific date range. To clarify / simplify, here's a simple table that represents my sheet:

COLUMN A COLUMN C COLUMN E

ROW 1 DATE AMT1 AMT2
ROW 2 9/22/2013 25.00
ROW 3 9/30/2013 1000.00
ROW 4 10/12/2013 50.00
ROW 5 10/19/2013 1250.00
ROW 6 10/28/2013 75.00
ROW 7 11/1/2013 1500.00
ROW 8 11/2/2013 100.00

So, what I want is to be able to add the values in AMT1 and/or AMT2 that fall between 9/1/13 - 9/30/13 and save that value in CELL A10.

Then the same thing, but between dates 10/1/13 - 10/31/13 and save in CELL A11.

... and again for 11/1/13 - 11/30/13 and save in CELL A12.


I hope this is clear. Thank you for helping!!

[InterstateRentals]

Darn, the formatting went bad!! Sorry.

Maybe this works better:

EXCEL sheet1.jpg

[Tony Valko]

Try this...


Data Range

	A	B	C	D	E
1	DATE	----	AMT1	----	AMT2
2	9/22/2013				25
3	9/30/2013		1000
4	10/12/2013				50
5	10/19/2013		1250
6	10/28/2013				75
7	11/1/2013		1500
8	11/2/2013				100
9
10	9/1/2013	1025
11	10/1/2013	1375
12	11/1/2013	1600

Enter the 1st of the month dates as shown.

Enter this formula in B10 and copy down as needed:

=SUMPRODUCT(--(TEXT(A$2:A$8,"mmmyy")=TEXT(A10,"mmmyy")),C$2:C$8+E$2:E$8)

[InterstateRentals]

Tony, that is absolutely awesome! I'm curious what the two dashes do at the front of the formula??

Thank you very much. Much appreciated!

[Tony Valko]

The SUMPRODUCT function works with numbers. It multiplies arrays of numbers then sums the result of that multiplication.

This expression returns an array of either TRUE or FALSE:

TEXT(A$2:A$8,"mmmyy")=TEXT(A10,"mmmyy")

Since SUMPRODUCT works with numbers we have to convert that array of TRUE or FALSE to numbers.

The double unary minus -- is one way to do that.

--TRUE = 1
--FALSE = 0

Then, SUMPRODUCT is able to do the array multiplication. It might look something like this:

1 * 25 = 25
1 * 1000 = 1000
0 * 50 = 0
0 * 1250 = 0
0 * 75 = 0
etc
etc

See this for more on the power of the SUMPRODUCT function:

http://xldynamic.com/source/xld.SUMPRODUCT.html

[InterstateRentals]

Nice explanation! I get it now. Thank you for taking the time!!

[Tony Valko]

You're welcome. Thanks for the feedback!