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
Bookmarks