+ Reply to Thread
Results 1 to 11 of 11

Sum Multiple columns with Named Range and single row criteria

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    5

    Sum Multiple columns with Named Range and single row criteria

    I used to consider myself advanced in excel but seem to be rusty or this is just not possible. I want to create a named range called "Q1" as criteria to sum Jan, Feb, Mar at every instance of the row criteria, "DMC". The entire data set has all 12 months and multiple lines of data but just using this as an example to simplify. Any help as always, is greatly appreciated.

    Thanks.



    Cost Code Jan Feb Mar

    DMC 100000 75000 85000
    Attached Files Attached Files
    Last edited by bgood1510; 04-17-2019 at 05:36 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Sum Multiple columns with Named Range and single row criteria

    i think that you will find that Q1 is a reserved name.


    Please attach a sample workbook.
    Last edited by mehmetcik; 04-17-2019 at 04:20 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    05-23-2017
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Sum Multiple columns with Named Range and single row criteria

    Thanks. Just added the sample file.

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sum Multiple columns with Named Range and single row criteria

    Try this:
    Please Login or Register  to view this content.
    You can't use the named range containing both dates, but you could set up 2 named ranges for a start date and end date. Does that give you the results you'd like to see?

  5. #5
    Registered User
    Join Date
    04-14-2019
    Location
    Donnybrook, Australia
    MS-Off Ver
    16
    Posts
    9

    Re: Sum Multiple columns with Named Range and single row criteria

    Hi Melvosh

    Awesome! Your formula works really well, and I am very impressed. However, I don't understand why the formula actually works. D10:D25 and E9:P9 are subsets of the array E10:P25, and therefore don't have the same number of elements. I did not see this use being described within the Excel help on Sumproduct(), and didn't find any use like this on a quick search of the internet.

    Logically you end up with something like ([1,2,3,4,5,6]*[1,2,3]*[1,2,3]), which should generate an error?

    Do you know how, or maybe why, does Excel track the positions of the subsets within the larger array to arrive at the correct answer?

    Cheers

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sum Multiple columns with Named Range and single row criteria

    This post over at ExcelJet provides a good explanation.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  7. #7
    Registered User
    Join Date
    04-14-2019
    Location
    Donnybrook, Australia
    MS-Off Ver
    16
    Posts
    9

    Re: Sum Multiple columns with Named Range and single row criteria

    It does indeed. I see the criterion generates a complete matrix not just the subset. Thanks

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sum Multiple columns with Named Range and single row criteria

    You're welcome, happy to help.

  9. #9
    Registered User
    Join Date
    05-23-2017
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Sum Multiple columns with Named Range and single row criteria

    Very cool. I can work with this. Thanks so much. You rock!

  10. #10
    Registered User
    Join Date
    05-23-2017
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Sum Multiple columns with Named Range and single row criteria

    One other quick question for you gurus. Any way to have it work on multiple row criteria as well?

  11. #11
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sum Multiple columns with Named Range and single row criteria

    You could add the criteria you're looking for in a fashion similar to (D10:D25=V10). It depends on how exactly you want it work. By default, it will work as an AND statement, so if the two criteria are mutually exclusive (i.e. they cannot both be true), you will end up with 0 results.

+ 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. If Statement with Multiple Named Range Criteria
    By Dormie in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-18-2018, 06:57 AM
  2. Countifs with multiple criteria in single criteria range
    By SUHAS KARHADKAR in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-01-2016, 10:55 AM
  3. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  4. [SOLVED] UDF for sum of multiple criteria in multiple columns and single criteria in multiple colum
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 11-22-2013, 04:26 AM
  5. Replies: 2
    Last Post: 03-05-2013, 03:07 PM
  6. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  7. Replies: 1
    Last Post: 06-08-2012, 01:28 PM

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