Sumifs absolute

[Number1One]

Hi All,
Been trying to make this work with previous similar questions but unable to make it work.

When trying to do this on a summary level the absolute is done on the top level and not on the line by line level and then summed, which is what i am looking for

For example If i do a cross grid with my data set i get absolute error on Supplier C as 102 (P9+P18). Whereas on a summary level this comes back as 42(H15), -30+70. Only 1 issue on this example data set but much more on my actual data.

I have a few criteria, which are generally triggers in top left or what the summary/column is, Trying to keep the Date column search dynamic for minimum upkeep and analysis on 4 week/3 month periods. Formula in Summary currently: =SUMIFS(INDEX($G$21:$L$52,0,MATCH($B$2,$G$20:$L$20,0)),$A$21:$A$52,$B$5,$B$21:$B$52,$B$3,$D$21:$D$52,$B$4,$C$21:$C$52,$D13,$F$21:$F$52,E$12)

1 A B C D E G H I J K L M N O 2 Triggers 3 Date 08/01/2019 Summary Broken Down Check 4 Data Z Total 161 206 -45 189 Total Total 161 206 -45 189 5 Type Food Product Sales Forecast Error Abs Error Product Supplier Sales Forecast Error Abs Error 6 Check 1 1 72 123 -51 51 1 A 28 41 -13 13 7 2 0 0 0 0 1 B 21 29 -8 8 8 3 17 83 -66 66 1 C 23 53 -30 30 9 4 0 0 0 0 2 B 0 0 0 0 10 5 72 0 72 72 2 C 0 0 0 0 11 Summary 2 2 E 0 0 0 0 12 Total 161 206 -45 129 2 F 0 0 0 0 13 Supplier Sales Forecast Error Abs Error 3 A 17 83 -66 66 14 A 45 124 -79 79 4 A 0 0 0 0 15 B 21 29 -8 8 4 B 0 0 0 0 16 C 95 53 42 42 4 C 0 0 0 0 17 D 0 0 0 0 5 C 72 0 72 72 18 E 0 0 0 0 19 F 0 0 0 0 20 Data 21 Check Data Supplier Type Product Metric 01/01/2019 08/01/2019 15/01/2019 22/01/2019 29/01/2019 05/02/2019 22 1 Z A Food 1 Sales 93 28 76 74 4 98 23 1 Z A Food 1 Forecast 1 41 90 45 83 88 24 1 X A Food 1 Sales 16 52 67 25 52 31 25 1 X A Food 1 Forecast 41 46 64 2 76 33 26 1 Z B Food 1 Sales 50 21 71 31 29 24 27 1 Z B Food 1 Forecast 19 29 92 73 55 13 28 1 X B Food 1 Sales 10 50 63 56 32 41 29 1 X B Food 1 Forecast 10 86 48 52 25 54 <tbody> </tbody>

<tbody>
</tbody>

 

[ValuationMan]

I'm not following exactly what your desired end result is, but I think I see something.

When I enter your formula in a workbook, the issue that jumps out to me is Criteria_range4 ($C$21:$C$52), and criteria4 ($D13). I'm pretty sure $D13 should be $C13.

 

[Number1One]

I'm not following exactly what your desired end result is, but I think I see something.

When I enter your formula in a workbook, the issue that jumps out to me is Criteria_range4 ($C$21:$C$52), and criteria4 ($D13). I'm pretty sure $D13 should be $C13.

Thanks, yeh thats my fault i didn't repost the formula after adding the 1234, abcd for guides.
Not sure how to Edit OP but here it is:
=SUMIFS(INDEX($H$22:$M$53,0,MATCH($C$3,$H$21:$M$21,0)),$B$22:$B$53,$C$6,$C$22:$C$53,$C$4,$E$22:$E$53,$C$5,$D$22:$D$53,$D14,$G$22:$G$53,E$13)

Goal is to get the correct Sum Absolute error, so i can use it in all my summaries automatically, rather than causing loads of calculations and then adding them up.
Currently its giving the wrong answer: Sum of all errors and then absoluting this answer.
Correct answer: Calculate absolute error per line of data and then sum it per summary triggers given.

Leaning toward some sort of sumproduct but it needs several conditions and not excessively resource heavy.

 

[Number1One]

The formula is from D14, Summary 2 as summary 2 shows the issue data: H16 should equal O8+O17
Similar formula in Summary 1 but different columns.

 

[ValuationMan]

I used the formula in D14 and I get 28, but your data shows 45. I assume 45 is wrong?

 

[Number1One]

45 is correct there its H16 where the answer is wrong.
Ah Your missing product 3, because the data got cut off:

20	A	B	C	D	E	G	H	I	J	K	L	M
21	Check	Data	Supplier	Type	Product	Metric	01/01/2019	08/01/2019	15/01/2019	22/01/2019	29/01/2019	05/02/2019
22	1	Z	A	Food	1	Sales	93	28	76	74	4	98
23	1	Z	A	Food	1	Forecast	1	41	90	45	83	88
24	1	X	A	Food	1	Sales	16	52	67	25	52	31
25	1	X	A	Food	1	Forecast	41	46	64	2	76	33
26	1	Z	B	Food	1	Sales	50	21	71	31	29	24
27	1	Z	B	Food	1	Forecast	19	29	92	73	55	13
28	1	X	B	Food	1	Sales	10	50	63	56	32	41
29	1	X	B	Food	1	Forecast	10	86	48	52	25	54
30	1	Z	C	Food	1	Sales	77	23	11	46	77	99
31	1	Z	C	Food	1	Forecast	6	53	5	22	24	51
32	1	Z	B	Packaging	2	Sales	8	56	85	39	50	89
33	1	Z	B	Packaging	2	Forecast	87	59	29	71	93	27
34	1	Z	C	Packaging	2	Sales	26	20	99	95	74	47
35	1	Z	C	Packaging	2	Forecast	8	42	38	92	17	77
36	1	X	C	Packaging	2	Sales	37	95	63	4	16	28
37	1	X	C	Packaging	2	Forecast	39	79	96	92	34	52
38	1	Z	E	Packaging	2	Sales	84	30	61	81	4	99
39	1	Z	E	Packaging	2	Forecast	95	65	31	75	3	28
40	1	Z	F	Packaging	2	Sales	35	100	92	36	50	54
41	1	Z	F	Packaging	2	Forecast	77	9	76	54	14	34
42	1	Z	A	Food	3	Sales	7	17	25	81	71	30
43	1	Z	A	Food	3	Forecast	40	83	93	79	84	73
44	0	Z	A	Food	4	Sales	19	12	76	73	41	11
45	0	Z	A	Food	4	Forecast	45	85	43	93	48	90
46	0	Z	B	Food	4	Sales	72	19	2	24	23	84
47	0	Z	B	Food	4	Forecast	43	6	60	74	37	5
48	0	Z	C	Food	4	Sales	74	63	16	87	8	76
49	0	Z	C	Food	4	Forecast	85	22	24	13	72	7
50	1	Z	C	Food	5	Sales	51	72	58	80	19	2
51	1	Z	C	Packaging	5	Forecast	62	23	38	41	22	7
52	1	X	C	Packaging	5	Sales	24	41	90	48	88	11
53	1	X	C	Packaging	5	Forecast	34	75	12	98	77	45

<colgroup><col><col><col><col><col span="3"><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Number1One]

Aware i didn't explain this very well in the first place, if anyone has any ideas on how to accomplish this or if there is more questions let me know.