Sum values in separate sheet based on two headings

1. 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

2. Re: Sum values in separate sheet based on two headings

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

3. 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
A\$2 refers to the sub-headers in the Dashboard tab

There are currently 1 users browsing this thread. (0 members and 1 guests)

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