Tricky Sum Formula 2 Horizontal Criteria, 1 Vertical

L

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
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.

[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria1[/TD]
[TD="class: xl65, width: 64"]Criteria2[/TD]
[TD="class: xl65, width: 64"]Criteria2[/TD]
[TD="class: xl65, width: 64"]Criteria2[/TD]
[TD="class: xl65, width: 64"]Criteria2[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[TD="class: xl65, width: 64"]Criteria3[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl66"]Criteria1[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl66"]Criteria2[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl66"]Criteria3[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 1088"]
<tbody>[TR]
[TD="class: xl64, width: 64"]Options[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64"]Criteria1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"]Criteria2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"]Criteria3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[TD="class: xl63"]Options[/TD]
[TD="class: xl63"]IAQ[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier1[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]48[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD="class: xl63, align: right"]27[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier2[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]43[/TD]
[TD="class: xl63, align: right"]37[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]44[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]27[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]44[/TD]
[TD="class: xl63, align: right"]39[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]48[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier3[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]33[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]38[/TD]
[TD="class: xl63, align: right"]45[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]27[/TD]
[TD="class: xl63, align: right"]46[/TD]
[TD="class: xl63, align: right"]32[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]26[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]21[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier5[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]45[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]36[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]27[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]44[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]49[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]27[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier6[/TD]
[TD="class: xl63, align: right"]48[/TD]
[TD="class: xl63, align: right"]42[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63, align: right"]37[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]38[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier7[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]43[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]39[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]32[/TD]
[TD="class: xl63, align: right"]36[/TD]
[TD="class: xl63, align: right"]48[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier8[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]42[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]39[/TD]
[TD="class: xl63, align: right"]42[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]32[/TD]
[TD="class: xl63, align: right"]43[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier9[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]26[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63, align: right"]45[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]48[/TD]
[TD="class: xl63, align: right"]46[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier10[/TD]
[TD="class: xl63, align: right"]42[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]39[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]43[/TD]
[TD="class: xl63, align: right"]46[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier11[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]48[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]47[/TD]
[TD="class: xl63, align: right"]46[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tier12[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63, align: right"]33[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD="class: xl63, align: right"]37[/TD]
[TD="class: xl63, align: right"]41[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63, align: right"]37[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63, align: right"]29[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63, align: right"]35[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]39[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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))

[TABLE="width: 1260"]
<tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[TD="class: xl63, width: 70"]H[/TD]
[TD="class: xl63, width: 70"]I[/TD]
[TD="class: xl63, width: 70"]J[/TD]
[TD="class: xl63, width: 70"]K[/TD]
[TD="class: xl63, width: 70"]L[/TD]
[TD="class: xl63, width: 70"]M[/TD]
[TD="class: xl63, width: 70"]N[/TD]
[TD="class: xl63, width: 70"]O[/TD]
[TD="class: xl63, width: 70"]P[/TD]
[TD="class: xl63, width: 70"]Q[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64, width: 70"]Tier[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl64, width: 70"]Tier1[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]6[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl64, width: 70"]Tier2[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]7[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64, width: 70"]Tier3[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]33[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]38[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl64, width: 70"]Tier4[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]26[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl64, width: 70"]Tier5[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]36[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]49[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl64, width: 70"]Tier6[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]17[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]7[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]14[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]38[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl64, width: 70"]Tier7[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[TD="class: xl64, width: 70"]36[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl64, width: 70"]Tier8[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl64, width: 70"]Tier9[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]9[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]26[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]6[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]13[/TD]
[TD="class: xl64, width: 70"]Tier10[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]9[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl64, width: 70"]Tier11[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]2[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]15[/TD]
[TD="class: xl64, width: 70"]Tier12[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]33[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]17[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl65"]97[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
admiral100 said:
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))

[TABLE="width: 1260"]
<tbody>[TR]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"]A[/TD]
[TD="class: xl63, width: 70"]B[/TD]
[TD="class: xl63, width: 70"]C[/TD]
[TD="class: xl63, width: 70"]D[/TD]
[TD="class: xl63, width: 70"]E[/TD]
[TD="class: xl63, width: 70"]F[/TD]
[TD="class: xl63, width: 70"]G[/TD]
[TD="class: xl63, width: 70"]H[/TD]
[TD="class: xl63, width: 70"]I[/TD]
[TD="class: xl63, width: 70"]J[/TD]
[TD="class: xl63, width: 70"]K[/TD]
[TD="class: xl63, width: 70"]L[/TD]
[TD="class: xl63, width: 70"]M[/TD]
[TD="class: xl63, width: 70"]N[/TD]
[TD="class: xl63, width: 70"]O[/TD]
[TD="class: xl63, width: 70"]P[/TD]
[TD="class: xl63, width: 70"]Q[/TD]
[/TR]
[TR]
[TD="class: xl63"]1[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[/TR]
[TR]
[TD="class: xl63"]2[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria1[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria2[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[TD="class: xl64, width: 70"]Criteria3[/TD]
[/TR]
[TR]
[TD="class: xl63"]3[/TD]
[TD="class: xl64, width: 70"]Tier[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[TD="class: xl64, width: 70"]Options[/TD]
[TD="class: xl64, width: 70"]IAQ[/TD]
[/TR]
[TR]
[TD="class: xl63"]4[/TD]
[TD="class: xl64, width: 70"]Tier1[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]6[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]5[/TD]
[TD="class: xl64, width: 70"]Tier2[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]7[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[/TR]
[TR]
[TD="class: xl63"]6[/TD]
[TD="class: xl64, width: 70"]Tier3[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]33[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]38[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[/TR]
[TR]
[TD="class: xl63"]7[/TD]
[TD="class: xl64, width: 70"]Tier4[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]26[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[/TR]
[TR]
[TD="class: xl63"]8[/TD]
[TD="class: xl64, width: 70"]Tier5[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]36[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]44[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]49[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]27[/TD]
[/TR]
[TR]
[TD="class: xl63"]9[/TD]
[TD="class: xl64, width: 70"]Tier6[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]17[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]7[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]14[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]38[/TD]
[/TR]
[TR]
[TD="class: xl63"]10[/TD]
[TD="class: xl64, width: 70"]Tier7[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[TD="class: xl64, width: 70"]36[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]11[/TD]
[TD="class: xl64, width: 70"]Tier8[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]12[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]32[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[/TR]
[TR]
[TD="class: xl63"]12[/TD]
[TD="class: xl64, width: 70"]Tier9[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]9[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]26[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]16[/TD]
[TD="class: xl64, width: 70"]45[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]6[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]13[/TD]
[TD="class: xl64, width: 70"]Tier10[/TD]
[TD="class: xl64, width: 70"]42[/TD]
[TD="class: xl64, width: 70"]9[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]39[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]28[/TD]
[TD="class: xl64, width: 70"]43[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]25[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]40[/TD]
[TD="class: xl64, width: 70"]24[/TD]
[TD="class: xl64, width: 70"]21[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[TD="class: xl64, width: 70"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"]14[/TD]
[TD="class: xl64, width: 70"]Tier11[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]2[/TD]
[TD="class: xl64, width: 70"]19[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]48[/TD]
[TD="class: xl64, width: 70"]31[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]47[/TD]
[TD="class: xl64, width: 70"]46[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]50[/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]15[/TD]
[TD="class: xl64, width: 70"]Tier12[/TD]
[TD="class: xl64, width: 70"]13[/TD]
[TD="class: xl64, width: 70"]18[/TD]
[TD="class: xl64, width: 70"]33[/TD]
[TD="class: xl64, width: 70"]23[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]41[/TD]
[TD="class: xl64, width: 70"]11[/TD]
[TD="class: xl64, width: 70"]17[/TD]
[TD="class: xl64, width: 70"]37[/TD]
[TD="class: xl64, width: 70"]20[/TD]
[TD="class: xl64, width: 70"]29[/TD]
[TD="class: xl64, width: 70"]34[/TD]
[TD="class: xl64, width: 70"]35[/TD]
[TD="class: xl64, width: 70"]22[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl65"]97[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Perfect mate, thanks so much!
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
80
djclements
djclements
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
256
Peter_SSs
Peter_SSs
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
422
Alex Blakenburg
A
A
  • Solved
Two-value combination extraction formula?
2
Replies
11
Views
188
bebo021999
B
K
  • Question Question
conditional formatting question
Replies
2
Views
585
kerm007
K

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

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
Back
Top