Tricky Sum Formula 2 Horizontal Criteria, 1 Vertical

[Lewzerrrr]

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.

Criteria1

Criteria1

Criteria1

Criteria1

Criteria1

Criteria1

Criteria2

Criteria2

Criteria2

Criteria2

Criteria3

Criteria3

Criteria3

Criteria3

Criteria3

Criteria3

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
	Criteria1						Criteria2				Criteria3
Tier	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ
Tier1	11	48	6	3	24	31	12	41	41	27	40	22	22	47	1	8
Tier2	1	43	37	8	16	40	4	44	7	27	28	44	39	11	4	48
Tier3	31	33	29	50	38	45	25	8	25	47	13	13	5	27	46	32
Tier4	4	40	12	24	26	1	18	40	20	18	8	28	47	15	31	21
Tier5	1	47	45	19	13	28	36	16	34	27	4	44	35	49	28	27
Tier6	48	42	12	17	37	11	7	15	14	34	21	3	40	4	29	38
Tier7	21	34	13	47	43	34	39	34	1	34	1	32	36	48	47	8
Tier8	25	42	13	13	34	12	3	10	23	39	42	23	15	11	32	43
Tier9	11	1	21	9	5	26	3	16	45	19	6	18	20	48	46	5
Tier10	42	9	18	39	20	28	43	46	31	25	41	40	24	21	15	15
Tier11	29	2	19	34	48	31	20	3	47	46	50	50	35	50	10	1
Tier12	13	18	33	23	41	37	41	11	17	37	20	29	34	35	22	39

<tbody>
</tbody>

 

[admiral100]

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))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Options
2		Criteria1	Criteria1	Criteria1	Criteria1	Criteria1	Criteria1	Criteria2	Criteria2	Criteria2	Criteria2	Criteria3	Criteria3	Criteria3	Criteria3	Criteria3	Criteria3
3	Tier	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ
4	Tier1	11	48	6	3	24	31	12	41	41	27	40	22	22	47	1	8
5	Tier2	1	43	37	8	16	40	4	44	7	27	28	44	39	11	4	48
6	Tier3	31	33	29	50	38	45	25	8	25	47	13	13	5	27	46	32
7	Tier4	4	40	12	24	26	1	18	40	20	18	8	28	47	15	31	21
8	Tier5	1	47	45	19	13	28	36	16	34	27	4	44	35	49	28	27
9	Tier6	48	42	12	17	37	11	7	15	14	34	21	3	40	4	29	38
10	Tier7	21	34	13	47	43	34	39	34	1	34	1	32	36	48	47	8
11	Tier8	25	42	13	13	34	12	3	10	23	39	42	23	15	11	32	43
12	Tier9	11	1	21	9	5	26	3	16	45	19	6	18	20	48	46	5
13	Tier10	42	9	18	39	20	28	43	46	31	25	41	40	24	21	15	15
14	Tier11	29	2	19	34	48	31	20	3	47	46	50	50	35	50	10	1
15	Tier12	13	18	33	23	41	37	41	11	17	37	20	29	34	35	22	3
	97

<tbody>
</tbody>

 

[Lewzerrrr]

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))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Options
2		Criteria1	Criteria1	Criteria1	Criteria1	Criteria1	Criteria1	Criteria2	Criteria2	Criteria2	Criteria2	Criteria3	Criteria3	Criteria3	Criteria3	Criteria3	Criteria3
3	Tier	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ	Options	IAQ
4	Tier1	11	48	6	3	24	31	12	41	41	27	40	22	22	47	1	8
5	Tier2	1	43	37	8	16	40	4	44	7	27	28	44	39	11	4	48
6	Tier3	31	33	29	50	38	45	25	8	25	47	13	13	5	27	46	32
7	Tier4	4	40	12	24	26	1	18	40	20	18	8	28	47	15	31	21
8	Tier5	1	47	45	19	13	28	36	16	34	27	4	44	35	49	28	27
9	Tier6	48	42	12	17	37	11	7	15	14	34	21	3	40	4	29	38
10	Tier7	21	34	13	47	43	34	39	34	1	34	1	32	36	48	47	8
11	Tier8	25	42	13	13	34	12	3	10	23	39	42	23	15	11	32	43
12	Tier9	11	1	21	9	5	26	3	16	45	19	6	18	20	48	46	5
13	Tier10	42	9	18	39	20	28	43	46	31	25	41	40	24	21	15	15
14	Tier11	29	2	19	34	48	31	20	3	47	46	50	50	35	50	10	1
15	Tier12	13	18	33	23	41	37	41	11	17	37	20	29	34	35	22	3
	97

<tbody>
</tbody>

Perfect mate, thanks so much!

 

[Lewzerrrr]

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.