Tricky Sum Formula 2 Horizontal Criteria, 1 Vertical

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
251
Hi,

Not sure about the best way to go about this like at all.. I have a table of data that comes in once a week that looks like the table below (but bigger).

Now I've made another table listing all the tiers down one column, and the criteria names across. The data needs to be the sum of the options for that Tier & Criteria, what's the best solution? The tier's can change aswell as criteria each week.. thought about inserting a total column between each Criteria change but this would take some time, thought it would be best to extract it using the Tier & Criteria values.. somehow :(

I've added a helper row to fill across the Criteria values so row 2 and 3 looks like this.

Criteria1Criteria1Criteria1Criteria1Criteria1Criteria1Criteria2Criteria2Criteria2Criteria2Criteria3Criteria3Criteria3Criteria3Criteria3Criteria3
Criteria1 Criteria2 Criteria3

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

Any help is HUGELY appreciated, some results I need would be like for Tier2 Criteria2 the total Options are 11, Tier6 Criteria1 total options are 97.

Options
Criteria1Criteria2Criteria3
TierOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQ
Tier11148632431124141274022224718
Tier2143378164044472728443911448
Tier3313329503845258254713135274632
Tier444012242611840201882847153121
Tier5147451913283616342744435492827
Tier648421217371171514342134042938
Tier721341347433439341341323648478
Tier82542131334123102339422315113243
Tier911121952631645196182048465
Tier104291839202843463125414024211515
Tier1129219344831203474650503550101
Tier1213183323413741111737202934352239

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi,

If you ok with helper row ...try this:-

Ctrl+Shift+Enter NOT just Enter

For Tier6 Criteria1 :

A19
=SUM(IF($A$4:$A$15=A9,IF($B$3:$Q$3="Options",IF($B$2:$Q$2="Criteria1",$B$4:$Q$15)),0))

ABCDEFGHIJKLMNOPQ
1Options
2Criteria1Criteria1Criteria1Criteria1Criteria1Criteria1Criteria2Criteria2Criteria2Criteria2Criteria3Criteria3Criteria3Criteria3Criteria3Criteria3
3TierOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQ
4Tier11148632431124141274022224718
5Tier2143378164044472728443911448
6Tier3313329503845258254713135274632
7Tier444012242611840201882847153121
8Tier5147451913283616342744435492827
9Tier648421217371171514342134042938
10Tier721341347433439341341323648478
11Tier82542131334123102339422315113243
12Tier911121952631645196182048465
13Tier104291839202843463125414024211515
14Tier1129219344831203474650503550101
15Tier121318332341374111173720293435223
97

<tbody>
</tbody>
 
Last edited:

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
251
Hi,

If you ok with helper row ...try this:-

Ctrl+Shift+Enter NOT just Enter

For Tier6 Criteria1 :

A19
=SUM(IF($A$4:$A$15=A9,IF($B$3:$Q$3="Options",IF($B$2:$Q$2="Criteria1",$B$4:$Q$15)),0))

ABCDEFGHIJKLMNOPQ
1Options
2Criteria1Criteria1Criteria1Criteria1Criteria1Criteria1Criteria2Criteria2Criteria2Criteria2Criteria3Criteria3Criteria3Criteria3Criteria3Criteria3
3TierOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQOptionsIAQ
4Tier11148632431124141274022224718
5Tier2143378164044472728443911448
6Tier3313329503845258254713135274632
7Tier444012242611840201882847153121
8Tier5147451913283616342744435492827
9Tier648421217371171514342134042938
10Tier721341347433439341341323648478
11Tier82542131334123102339422315113243
12Tier911121952631645196182048465
13Tier104291839202843463125414024211515
14Tier1129219344831203474650503550101
15Tier121318332341374111173720293435223
97

<tbody>
</tbody>

Perfect mate, thanks so much!
 

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
251
Is this possible to do but with AVERAGE? I tried to replace SUM with AVERAGE but didn't work.

I need it to look at "<>0" which I know I can do with AVERAGEIFS but unsure how to place it in the array formulas with the IFS.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,115
Messages
5,599,809
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top