sumproduct values in multiple columns?

[wardrevcu]

I have spending data that are entered daily into one table under the column headings:
Date, Item, Cash_amt, Cap1_amt, Visa_amt

There are duplicate dates, duplicate "item" names, and the $ values for any given item can fall into 3 different columns but on different rows. See the picture link. (excelquestion01)

I need to sum all $ values for a certain item for a certain date range. The final sum will go into a different table on a different worksheet.

I tried using SUMPRODUCT, but its only returning the $ value for the "first" item name it finds in the column. Here's my formula:

=IF((OR(Daily_Spending2014[Date]>=$M$2,Daily_Spending2014[Date]<=$N$2)),SUMPRODUCT(VLOOKUP(L5,Daily_Spending2014!$D$4:$G$13,{2,3,4},0)),"")

I also tried the following formula but it didn't work either:

=SUMPRODUCT((Daily_Spending2014[Date]>=$M$2)*(Daily_Spending2014[Date]<=$N$2)*(Daily_Spending2014[Item]=$L$5)*(Daily_Spending2014[Cash_amt])*(Daily_Spending2014[CAP1_amt])*(Daily_Spending2014[VISA_amt]))

I can send you an Excel file if you need it.

Thanks!

 

[RonB1111]

Try this format:

=SUMPRODUCT(--(Daily_Spending2014[Date]>=$M$2),--(Daily_Spending2014[Date]<=$N$2),--(Daily_Spending2014[Item]=$L$5),Daily_Spending2014[Cash_amt]+Daily_Spending2014[CAP1_amt]+Daily_Spending2014[VISA_amt])

 

[wardrevcu]

Try this format:

=SUMPRODUCT(--(Daily_Spending2014[Date]>=$M$2),--(Daily_Spending2014[Date]<=$N$2),--(Daily_Spending2014[Item]=$L$5),Daily_Spending2014[Cash_amt]+Daily_Spending2014[CAP1_amt]+Daily_Spending2014[VISA_amt])

Plus signs...geez! I never thought of that...go figure. That worked perfectly, thanks a lot!

 

[RonB1111]

You're welcome!

 

[Peter_SSs]

Welcome to the MrExcel board!

Since the columns to sum are adjacent, you could also use this slightly more compact form.

=SUMPRODUCT((Daily_Spending2014[Date]>=$M$2)*(Daily_Spending2014[Date]<=$N$2)*(Daily_Spending2014[Item]=$L$5)*Daily_Spending2014[[Cash_amt]:[VISA_amt]])

 

[RonB1111]

Peter,
Thank you, I wasn't aware of that.
Ron