# Sumifs absolute

#### Number1One

##### New Member
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>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

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.

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.

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

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>

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.

Replies
7
Views
178
Replies
2
Views
193
Replies
7
Views
159
Replies
8
Views
70
Replies
2
Views
110

1,202,976
Messages
6,052,879
Members
444,606
Latest member
rwmhr

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

### Which adblocker are you using?

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

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