+ Reply to Thread
Results 1 to 14 of 14

Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    FL - USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Hi I'm new to the forum - There is a ton of great information on here but I can't seem to figure this one out.

    I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year. =SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13"). I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.

    Any advice would be appreciated.

    Thank you sincerely,

    Rick

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    You could still do it with SUMIFS, assuming you have a column with customer names.

    Assuming
    A1 = Start Date
    B1 = End Date
    C1 = Customer Name
    and column C has customer names..


    =SUMIFS(J2:J12904,E2:E12904,">"&A1,E2:E12904,"<"&B1,C2:C12904,C1)

    The SUMPRODUCT is similar and they're both technically array formulas (but behind the scenes)

    =SUMPRODUCT((E2:E12904>A1)*(E2:E12904<B1)*(C2:C12904=C1)*(J2:J12904))
    Last edited by daffodil11; 01-02-2014 at 05:53 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

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

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    If you're already using filters....

    why not Filter Column E for Greater than 12/31/11 AND Less than 1/1/13
    and then filter for the customer.

    And use
    =SUBTOTAL(9,J2:J12904)

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Hello Rick, do you mean you want to filter by a specific customer and for your SUMIFS formula to only include the visible data?

    The simple way is to add a helper column, then you only need a small modification to your SUMIFS.

    Assuming column Z is empty you can put this formula in Z2 copied down the column

    =SUBTOTAL(2,J2)

    that will only display 1 on rows where column J is populated with a number and the row is visible

    then add a condition to your SUMIFS like this

    =SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13",Z2:Z12904,1)
    Audere est facere

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

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Quote Originally Posted by daddylonglegs View Post
    Assuming column Z is empty you can put this formula in Z2 copied down the column

    =SUBTOTAL(2,J2)

    that will only display 1 on rows where column J is populated with a number and the row is visible

    then add a condition to your SUMIFS like this

    =SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13",Z2:Z12904,1)
    That is really super cool.

    I've always seen super complicated sumproduct formulas with subtotal in it.
    And honestly, I never mastered that kind of formula.
    This is way easier.

    Thanks.

  6. #6
    Registered User
    Join Date
    01-02-2014
    Location
    FL - USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Thank you all so much for the help - I am really appreciative.

    This is an example of the sheet attached:

    My formula wont ignores the filter, even when I add the helper column.

    Thank you all again....sample SUMIFS.xlsx

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Super complicated SUMPRODUCT to the rescue!

    I also stole some of DLL logic, because that stuff was just awesome.

    I5:

    =SUBTOTAL(2,H5)

    and copied downwards

    H18:

    =SUMPRODUCT((YEAR($C$5:$C$16)=G18)*($I$5:$I$16=1)*($H$5:$H$16))

    And copied downwards.

    Filter the data, watch the sums change.


    Couldn't figure out the SUMIF version.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    OK, I added a helper column and adjusted the formulas - now if you filter the SUMIFS results will change so that they reflect the visible data only - is that what you need?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-02-2014
    Location
    FL - USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Fantastic - Thank you so much, that is awesome!

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

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    welcome to the forum, Rick. here's the complicated formula Jonmo was mentioning i guess.
    personally prefer to work without helper columns if possible. but to each his own:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

    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

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

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    I'll take the helper column with Sumifs over Sumproduct with the volatile Offset and Row Functions any day.

    Helper columns aren't a 'bad' thing.
    We have 16k+ columns to work with, might as well use a few of them.

    But you're right, to each his own.
    The more options available, the better.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Quote Originally Posted by Jonmo1 View Post
    ...the volatile Offset and Row Functions any day.
    The ROW function is not volatile.

    http://www.decisionmodels.com/calcsecretsi.htm
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Quote Originally Posted by Tony Valko View Post
    The ROW function is not volatile.
    That's debateable.

    Row being volatile or not is a moot point in this case, because Offset is volatile.
    So I'll still prefer the helper column + sumifs.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Converting from SUMIFS to SUMPRODUCT in order to make view filtered results

    Quote Originally Posted by Jonmo1 View Post
    That's debateable.
    It's easy to test.

    Open a new workbook.

    Enter this formula in any cell:

    =ROW()

    Save the file and close it.
    Reopen the file.
    Wait a few seconds then close the file.

    If the ROW function was volatile Excel would have asked you if you wanted to save the changes you made to the file.

+ 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. [SOLVED] Using SUMPRODUCT and SUBTOTAL to count filtered results containing #N/A values
    By Powerslave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2013, 12:07 PM
  2. [SOLVED] Using sumifs for a filtered range
    By liranbo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-01-2012, 12:09 PM
  3. Replies: 1
    Last Post: 04-08-2012, 04:59 AM
  4. sumifs and filtered data
    By finance14 in forum Excel General
    Replies: 3
    Last Post: 12-22-2011, 11:36 AM
  5. [SOLVED] How do i view only the filtered results from autofilter ?
    By simon in forum Excel General
    Replies: 3
    Last Post: 08-16-2006, 10:10 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