+ Reply to Thread
Results 1 to 23 of 23

SUMIFs across multiple columns (SUMPRODUCT?)

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    SUMIFs across multiple columns (SUMPRODUCT?)

    Hello,

    Hoping for some help with this:

    =SUMIFS('2007-2014-15 stats'!D:H,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015")

    I tried entering as an array, and that isn't working. Columns have text headers so I am not sure SUMPRODUCT will work either and I'd like to not have to specify rows as the sheet continues to grow.

    Trying to sum the values in columns D to H depending on the date value (by month) in column C.

    Thanks.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,333

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    May be:


    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    That's giving me a value error, probably because of the text headers on the columns?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn



  5. #5
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    I tried to omit the rows with text in the ranges and I got a "0" value when using sumproduct.

    I will upload a book, give me minute as the one I am working off is huge, so I will have to recreate.

    Thanks!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Quote Originally Posted by source View Post
    That's giving me a value error, probably because of the text headers on the columns?
    Yes, there cannot be any text values in the Sum Range
    Use Defined ranges like D2:H100

    It's very poor performance anyway to use entire column references with sumproduct.

  7. #7
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    See attached
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    I tried specifying a range with the SUMPRODUCT and got 0 with bebo's suggestion.

    I am trying to use entire column references because of the size of the data and the fact that the data will continue to grow for a long period of time.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Again, use defined ranges.
    I see the data in the other sheet goes from row 6 to 66

    also, unlike Sumif(s), Sumproduct doesn't convert the text string "1/1/2015" to a useable date.
    It literally treats it like a text string.
    You have to convert that string to a date, commonly done by adding 0

    Try
    =SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>="1/1/2015"+0)*('2007-2014-15 stats'!$C$6:$C$69<"2/1/2015"+0)*('2007-2014-15 stats'!$D$6:$H$69))

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Quote Originally Posted by source View Post
    I am trying to use entire column references because of the size of the data and the fact that the data will continue to grow for a long period of time.
    Look into using Dynamic Named Ranges
    http://www.contextures.com/xlNames01.html

  11. #11
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    I should also mention I inherited this mess, and am trying to fix a number of issues with formulas in the sheet and simplify things without totally starting from scratch so that the users can continue to update their stats without having to be retrained from how they learned it.

    I get to reinvent the wheel in the coming fiscal year and will do away with this nonsense and will be generating stats with pivot tables and pivot charts. Thank goodness.

  12. #12
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    will SUMPRODUCT work if I use the DATE function? My first attempts were using the DATE function, ">="&DATE(1,1,2015)

  13. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    You don't need the concatenation with the quotes like you do in sumifs

    Try just
    Range>=DATE(...)

  14. #14
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    thanks for all the replies, so is it possible to use SUMIFS at all?

    basically, I would have to use a dynamic range to use SUMIF?

    So I would have to create a named table from D5:H? and then use in the formula like

    =SUMIFS(namedtable,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015") or would I used =SUMIFS(namedrange....

    how do I make the named table infinite?

    Sorry if I'm sounding dumb here, I'm just trying to wrap my head around using a dynamic range.

  15. #15
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    =SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>=DATE(1,1,2015)*('2007-2014-15 stats'!$C$6:$C$69<DATE(2,1,2015)*('2007-2014-15 stats'!$D$6:$H$69))))

    This gave me 0... weird.

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Sumifs Can't do it because it requires all the ranges (the sum range, and the criteria ranges) to be the same size/shape.
    Your criteria are in 1 column Each, but the range you're summing is 5 columns.

    No, you don't HAVE to use named ranges.
    However, there cannot be any TEXT values in the Sum Range, therefor you can't use entire columns (because you have headers in row 1 presumably)
    So you have to specify row #s in your ranges C2:C100.

    You could do C2:C1048576
    That is pretty much an entire column, minus row 1.

    However, also unlike Sumifs, sumproduct processes the ENTIRE range (sumifs only processes rows within the sheet's effectively used range)
    So doing entire columns creates ALOT of unnecessary work for Excel (by processing thousands of blank rows), resulting in poor performance.

    The best solution is to use Dynamic Named Ranges. These are able to adjust by themselves as your data grows.
    So you don't have to hardcode row#s in each range.

    You would create 2 named ranges.
    1 for the D:H range, 1 for column C

    Then your formula is
    =SUMPRODUCT((name1forC>="1/1/2015"+0)*(name1forC<"2/1/2015"+0)*(name2forDtoH))
    Last edited by Jonmo1; 01-26-2015 at 02:01 PM.

  17. #17
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Quote Originally Posted by source View Post
    =SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>=DATE(1,1,2015)*('2007-2014-15 stats'!$C$6:$C$69<DATE(2,1,2015)*('2007-2014-15 stats'!$D$6:$H$69))))

    This gave me 0... weird.
    Misplaced parens...

    Try
    =SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>=DATE(1,1,2015))*('2007-2014-15 stats'!$C$6:$C$69<DATE(2,1,2015))*('2007-2014-15 stats'!$D$6:$H$69))

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    I got there, too - but fractionally too late. This might be more user-friendly, though. It picks up the dates from B16-27.
    Attached Files Attached Files

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,988

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    The bottom line is that SUMRODUCT doesn't like dates entered as dates... Hard code them (as Jonmo did) or refer to them in another cell (as I did).
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Okay,

    So easiest solution I found was to make a total column (I) adding up the rows in columns D to H, This allows me to use SUMIF. Perhaps it's inelegant, but seems the easiest.

    =SUMIFS('2007-2014-15 stats'!I:I,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015")

  21. #21
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Quote Originally Posted by source View Post
    Okay,

    So easiest solution I found was to make a total column (I) adding up the rows in columns D to H, This allows me to use SUMIF. Perhaps it's inelegant, but seems the easiest.
    I wouldn't call that inelegant at all.
    In fact, it's probably an even better solution (I probably should have suggested it in the first place).


    That's called a 'Helper Column'
    And those are not a bad thing, because they 'help'.
    Excel gives you over 16000 columns, there's nothing wrong with using a few of them.

    Also, I find that 9 times out of 10, after creating a formula that does all the intermidiate calculations in 1 formula..
    Shortly after I realize, hey it sure would be nice to also see those intermediate calculations as well.

    So the results of SUM(D2:H2) and SUM(D3:H3) and SUM(D4:H4) etc.., might be usefull information later down the line in your sheet anyway.
    Last edited by Jonmo1; 01-26-2015 at 02:23 PM.

  22. #22
    Registered User
    Join Date
    11-05-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2003
    Posts
    56

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    Thanks for all your help! I learned lots.

    Like I said, I inherited this mess... I see they used a total column (Q) for pages, so why they didn't put one for documents is beyond me.
    Last edited by source; 01-26-2015 at 02:27 PM.

  23. #23
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIFs across multiple columns (SUMPRODUCT?)

    You're welcome.

+ 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. SUMIFS or SUMPRODUCT with Multiple Columns and with Date Criteria
    By eac8423 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2014, 05:20 PM
  2. [SOLVED] Sumifs or Sumproduct With Multiple Conditions Help Please!
    By geepee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2013, 09:27 AM
  3. Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?
    By Carcophan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2013, 01:43 PM
  4. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  5. [SOLVED] sumproduct and sumifs to create or statement for two columns help
    By draconius in forum Excel General
    Replies: 1
    Last Post: 09-22-2012, 04:18 AM

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