Sum If Array Formula

[mswaim85]

I am hoping someone can help me with a formula for my sales spreadsheet. I need an array formula that will add up all cash sales within a date range. I have attached a picture of the sheet I am working with for this formula. I found a formula that gets the correct answer, but it is extremely long and because this is for weekly sales, I would like to find an array formula that will get the same results with less typing.

Basically here is the working formula that I would like to shorten, and I am assuming this can be done as an array:

=SUM(IF(AND(E4>=DATE(2013,10,1),E4<=DATE(2013,10,7)),(IF(L4="cash",(F4+G4+H4),0)),0),(IF(AND(E5>=DATE(2013,10,1),E5<=DATE(2013,10,7)),(IF(L5="cash",(F5+G5+H5),0)),0)),(IF(AND(E6>=DATE(2013,10,1),E6<=DATE(2013,10,7)),(IF(L6="cash",(F6+G6+H6),0)),0)),(IF(AND(E7>=DATE(2013,10,1),E7<=DATE(2013,10,7)),(IF(L7="cash",(F7+G7+H7),0)),0)),(IF(AND(E8>=DATE(2013,10,1),E8<=DATE(2013,10,7)),(IF(L8="cash",(F8+G8+H8),0)),0)),(IF(AND(E9>=DATE(2013,10,1),E9<=DATE(2013,10,7)),(IF(L9="cash",(F9+G9+H8),0)),0))))

Here are a couple formulas that I have tried with no luck:

{=SUM(IF(AND(E4:E100>=DATE(2013,10,1),E4:E100<=DATE(2013,10,7)),(IF(L4:L100="cash",(sum(F4:H100),0)),0)}

{=SUMIFS(F4:H100, E4:E100>=DATE(2014,1,1),E4:E100<=DATE(2014,1,6),L4:L100,"cash")}


Hopefully this helps explain what I am trying to accomplish. Thanks in advance for the help.

prntsc excl.jpg

[benishiryo]

welcome to the forum, mswaim. i didn't try to decipher your 1st formula since it's too long. i don't know why you make it an array either. you can read about array formulas here:
http://www.cpearson.com/excel/ArrayFormulas.aspx

but since your formulas below doesn't work, you should probably tell us in layman's terms what you are trying to do. for eg. sum up Column F to H if column E is within a date range and Column L is equals to cash? if so, try:
=SUMPRODUCT(($E$4:$E$100>=DATE(2013,10,1))*($E$4:$E$100<=DATE(2013,10,7))*($L$4:$L$100="cash")*$F$4:$H$100)

SUMIFS require all the ranges to be of the same number of rows and columns. F4:H100 contains 3 columns. E4:E100 only contains 1.

if it doesn't work, i suggest you upload an Excel file in the thread. To do that, Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
and to illustrate your example better, click on How To Get Quick & Good Answers

[mswaim85]

Thanks for the quick reply. That formula didn't work though..." I need a formula that only add up the sales of each row that meet the criteria specified - if the date in column E is within the specified range and if the word in column L is "cash". The formula would need to check this criteria for each row 4 - 100 and add up the corresponding sales numbers from the rows that meet the two specified criteria (date range and "cash")

The formula that I am currently repeating but changing the line number for each line 4 - 100 is below.

=SUM(IF(AND(E4>=DATE(2013,10,1),E4<=DATE(2013,10,7)),(IF(L4="cash",(F4+G4+H4),0)),0),"

Although this formula works and gets me the results I need, it is extremely long and cumbersome.

hopefully this better explains what I am trying to accomplish.