+ Reply to Thread
Results 1 to 3 of 3

Sum values in separate sheet based on two headings

  1. #1
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    Sum values in separate sheet based on two headings

    I have two worksheets - 'Dashboard' and 'Source'.

    I have a table of data in both sheets that is organised using two rows of headings:

    Row 1 - Category 1, Category 2, Category 3

    ...and then underneath each category:

    Row 2: Category 1a, Category 1b, Category 1c, Category 1d,.... and then..... Category2a, Category2b, Category2c, Category 2d, etc

    Please note - In 'Source' sheet, each category ('Category 1', 'Category 2', etc) can appear more than once.

    In the 'Dashboard' sheet, I need to sum the values found in my table of data in 'Source' sheet based on whether or not they sit under both 'Category 1' (in Row 1), and 'Category 1a' (in Row 2). I then need to do the same for 'Category 1' and 'Category 1b', etc. Essentially I think I need to use a nested index, but I don't know how to do it.

    Any help on this would be greatly appreciated!

    Unfortunately I can't attach a file on my work computer...

    Thanks
    Last edited by shaunguyver; 10-03-2017 at 06:54 AM.

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Sum values in separate sheet based on two headings

    Hello, if you can attach your worksheet , it will be great

  3. #3
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    Re: Sum values in separate sheet based on two headings

    Hi - I have actually solved it. The formula I used is:

    =SUMIFS('Source'!$A$3:$Z$3,'Source'!$A$1:$Z$1,"*1*",'Source'!$A$2:$Z$2,A$2)

    'Source'!$A$3:$Z$3 refers to the numbers to be summed
    'Source'!$A$1:$Z$1 refers to the 'Category 1' 'Category 2' headers
    'Source'!$A$2:$Z$2 refers to the subheaders
    A$2 refers to the sub-headers in the Dashboard tab

+ 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] Macro to combine duplicate Rows Based on a particular Column to separate INV Headings
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-28-2017, 06:38 AM
  2. [SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values
    By jakecutler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 03:24 AM
  3. Need to select data based on conditions in a separate sheet then return the values
    By scottmcclean in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2014, 03:39 PM
  4. Trying to create a new sheet based on column headings selected
    By davidhale87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 01:47 PM
  5. Replies: 1
    Last Post: 04-04-2013, 02:47 PM
  6. Replies: 3
    Last Post: 05-27-2010, 10:57 AM
  7. Replies: 4
    Last Post: 06-03-2009, 04:59 PM

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