+ Reply to Thread
Results 1 to 3 of 3

Sum If Array Formula

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Costa Mesa, California
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Sum If Array Formula

    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

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Sum If Array Formula

    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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    01-02-2014
    Location
    Costa Mesa, California
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sum If Array Formula

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  2. Typing an array into a formula rather than referring to an array of cells
    By Cookstein2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-03-2013, 12:59 PM
  3. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  4. Converting 3x10 array to a 1X30 array to run a Match formula
    By NBVC in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2009, 07:45 AM
  5. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1