+ Reply to Thread
Results 1 to 16 of 16

Sumifs with nested index match formula help!

  1. #1
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Sumifs with nested index match formula help!

    Hi there,

    Trying to match two criteria (across rows and columns) using a sumifs formula.

    For cell D3, I'm wanting to do a sum ifs on the table D8-F31 to return the sum of all the values that match C3 in B8-B31 and only if the value in D7-F7 match D3...

    Would love any help!
    Attached Files Attached Files
    Last edited by palmbeach; 06-30-2020 at 08:55 AM.

  2. #2
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    If there's a duplicate thread, please let me know!!

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    Administrative Note:

    It has been only 20 minutes since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. Thanks for your understanding and patience.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    Yes, of course! I just wanted to put it out there that there may well be an existing thread and that I absolutely do not want to waste anyone's time re-writing an answer if they have seen something similar.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    Try this:

    =SUMPRODUCT(($B$11:$B$46=C3)*($D$10:$F$10=B3),$D$11:$F$46)

  6. #6
    Registered User
    Join Date
    03-03-2019
    Location
    Dhaka
    MS-Off Ver
    MS 2016
    Posts
    2

    Re: Sumifs with nested index match formula help!

    Type Cell D3:
    =SUMPRODUCT(($D$11:$F$46)*($B$11:$B$46=C3)*($D$10:$F$10=B3))

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    Thank you for helping us to help you.If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Administrative Note:

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

  8. #8
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Question Re: Sumifs with nested index match formula help!

    Thank you both very much for your speedy responses aliGW and ButterflyAsif!

    Would there be any reason why either of those sumproduct formulas would not work across different tabs? In the real file I'm working on** (i.e. not the sample I uploaded) the cells I'm trying to sum (i.e. the equivalent of $D$11:$F$46) are in a separate tab to everything else, and neither of the formulas seem to work. I tested that I understood them by typing them out in the sample file.

    ** I know the guidelines are to not upload confidential information, so I created a sample spreadsheet that's very similar so I could describe my problem here. Sorry if that's not the right thing to do!

  9. #9
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    Updating location now AliGW

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    Please provide sample data that properly mimics your own set-up. Desensitised, obviously!

  11. #11
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    Hi AliGW,

    Really appreciate the help, and sorry for not providing this spreadsheet the first time around - thought I was doing the right thing!

    Here's the attachment. Cell D3 (output tab) has the sumifs formula I've tried to use, to capture all of the data from the last 12 months on the data tab. It's only returning the first value in the array that matches the year (LTM) and the metric (up the top). I'm not sure how to get it so that it sums all the values that are in the same year within the column that matches the metric I'm looking at.

    Does that make sense?

    Any help greatly appreciated. I'm going to head to bed shortly, so I may not be able to reply until the morning unfortunately. This doesn't mean that I'm not super grateful for any help anyone tries to provide on this forum!

    Thank you!!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    I'd better be quick, then!

    =SUMPRODUCT((Data!$A$14:$A$233=Output!C3)*(Data!$C$4:$N$4=Output!B3),Data!$C$14:$N$233)

    Goodnight!

  13. #13
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    OK amazing - I will try that on my non-clean version right now.

    It sounds like sumifs wasn't the right approach here in that case - do you agree?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

    Yes, because you have a two-way matrix - COUNTIFS and SUMIFS are no good for that.

  15. #15
    Registered User
    Join Date
    06-30-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Sumifs with nested index match formula help!

    I did have a sumifs there, and couldn't do anything to get it to work.

    Your sumproduct worked amazingly and I now know how to use sumproduct for something other than weighted averages, so that's excellent.

    Thank you!


  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Sumifs with nested index match formula help!

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

+ 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. Nested index/match formula
    By Emosh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2020, 10:54 PM
  2. [SOLVED] Nested MID formula in Index Match?
    By Miles6978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-26-2019, 11:09 AM
  3. [SOLVED] Pls help with a sumifs index match formula
    By Roma1r in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2019, 06:01 AM
  4. [SOLVED] Formula Sumifs with Index Match not working
    By vba1234 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-15-2016, 06:37 AM
  5. Need help with Sumifs, Index, and match formula!
    By relmasri in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2016, 10:27 AM
  6. [SOLVED] Dynamic Array, Index & Match, SUMIFS Formula Help!
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-03-2014, 09:27 PM
  7. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 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