+ Reply to Thread
Results 1 to 5 of 5

SUMIFS for dynamic range

  1. #1
    Forum Contributor
    Join Date
    02-09-2015
    Location
    malta
    MS-Off Ver
    Microsoft 365
    Posts
    383

    SUMIFS for dynamic range

    Hi guys,

    I would like to ask whether someone can help me out with the below.

    Basically I have a matrix with currencies (e.g. Eur) on the y-axis (columns) and name of cities on the x-axis (rows).

    I would like to sum all instances of e.g. EUR against Milan. Usually a simple sumif will do the trick. However my problem is that the range from where the sumif will be picking up is going to be dynamic. That is, example, 'Milan' will not always be in column E.

    For ease of reference I have attached a sample.

    Is there a way I can go about it? Not sure if a sumproduct could be used, or any other alternative which is not as slow as an array?

    Much appreciated and thanks a lot for your help.

    Best Regards

    Keibri
    Attached Files Attached Files

  2. #2
    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,396

    Re: SUMIFS for dynamic range

    Use SUMPRODUCT:

    =SUMPRODUCT(($B$6:$B$10=I6)*($C$5:$E$5=J5),$C$6:$E$10)
    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.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: SUMIFS for dynamic range

    Here is a pivot table solution, doesn't matter where your columns are.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-09-2015
    Location
    malta
    MS-Off Ver
    Microsoft 365
    Posts
    383

    Re: SUMIFS for dynamic range

    Thanks guys
    Kersplash thanks for your suggestion, I was more inclined to use formulas but thanks a lot
    AliGW thanks a lot your suggestion works wonders
    Cheers guys, always appreciate your kind help!

  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,396

    Re: SUMIFS for dynamic range

    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. [SOLVED] Dynamic range sumifs
    By eggselent in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-24-2016, 12:53 PM
  2. Replies: 3
    Last Post: 12-09-2015, 03:10 AM
  3. Optimize SUMPRODUCT with dynamic sum range. SUMIFS??
    By ron2k_1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2015, 05:33 PM
  4. [SOLVED] SUMIFS with a dynamic, offset criteria range
    By adelcap in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2014, 05:35 AM
  5. [SOLVED] Dynamic range in SUMIFS
    By chungliwen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-14-2013, 03:28 AM
  6. [SOLVED] sumifs function to calculate dynamic range sum problem
    By xs2deepak in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-25-2013, 04:24 PM
  7. Replies: 0
    Last Post: 10-12-2012, 01:58 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