# Sumifs with multiple criteria placed in first tablerow, where criterias match

#### Doge Robert

##### New Member
Hello there.

I have a table with budgets for various suppliers and their locations (where they supply, what we're buying).
I also have three different ways of ordering their budgetary numbers into categories. (Suppliers version, our version and governments version)

I'm trying to do sums across the budgetary categories and then write the sum in the topmost row (in the sumcolumn for each category), which match all the criteria.

I've tried illustrating, what I need the sollution to look like in the table, with colorcoding to explain, how each sum is calculated.)

I've tried making a solution with array formulas and indirect (to get the dynamic cell ranges right), but I can't get it to work.

Example of sollution:
In the column BudgetCategory A Sum, I need to calculate the sum of the values in the budgetcolumn, corresponding to the the categories in the BudgetCategory A column for each matching set of supplier and location. So for Supplier A, Location A, Cat A B.
I can do it, but then the sum would appear in every row, where there is a matching set. So three times for Supplier A, Location A, Cat A B.

Is there a way to have the formula calculated for all matching sets but the result printed only in the topmost instance of each matching set?

I can't do it with pivottables, as I need the calculated sums for some other formulas first.

Note also that I'm using a table, as I'll have to add multiple suppliers and locations for each over time, so the ranges must be dynamic.

Is this even possible?

(I'm using Excel in Danish and working with confidential data, so I cannot upload an actual example)

I hope you can help me..

Kind Regards

Robert.

#### Attachments

• Excel Example.PNG
60 KB · Views: 3

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Fluff

##### MrExcel MVP, Moderator
Maybe something like
Excel Formula:
``=IF(COUNTIFS(C\$2:C2,C2)=1,SUMIFS(F:F,C:C,C2),"")``

#### Eric W

##### MrExcel MVP
In table nomenclature:

Book1 (version 1).xlsb
CDEFGHIJK
1SupplierLocatonBudget Category ABudget Category BBudget Category CBudgetBudgetCategory A SumBudgetCategory B SumBudget Category C Sum
2Supplier ALocation ACat A ACat B ACat C A20002000142353500
3Supplier ALocation ACat A BCat B ACat C A150012000
4Supplier ALocation ACat A BCat B ACat C B10000  20750
5Supplier ALocation ACat A BCat B BCat C B500 12450
6Supplier ALocation ACat A CCat B BCat C B750012980
7Supplier ALocation ACat A CCat B BCat C B2750
8Supplier ALocation ACat A CCat B BCat C C1800  1800
9Supplier ALocation ACat A CCat B CCat C D930 2043021631
10Supplier ALocation ACat A DCat B CCat C D200019500
11Supplier ALocation ACat A DCat B CCat C D17500
12Supplier ALocation ACat A ECat B DCat C D20168716136
13Supplier ALocation ACat A ECat B DCat C D1000
14Supplier ALocation ACat A ECat B ACat C E735  735
15Supplier ALocation ACat A ECat B DCat C F1715  1715
16Supplier ALocation ACat A ECat B DCat C G3220  3172.7
17Supplier ALocation ACat A FCat B ECat C G17.7-47.352.7
18Supplier ALocation ACat A FCat B ECat C G35
19Supplier ALocation ACat A FCat B BCat C G-100
Sheet33
Cell Formulas
RangeFormula
I2:I19I2=IF(COUNTIF(INDEX([Budget Category A],1):[@[Budget Category A]],[@[Budget Category A]])=1,SUMIF([Budget Category A],[@[Budget Category A]],[Budget]),"")
J2:J19J2=IF(COUNTIF(INDEX([Budget Category B],1):[@[Budget Category B]],[@[Budget Category B]])=1,SUMIF([Budget Category B],[@[Budget Category B]],[Budget]),"")
K2:K19K2=IF(COUNTIF(INDEX([Budget Category C],1):[@[Budget Category C]],[@[Budget Category C]])=1,SUMIF([Budget Category C],[@[Budget Category C]],[Budget]),"")

Note that all 3 formulas can be combined into one, with INDIRECT and some manipulation, but I'm not so sure that's a good thing.

#### Doge Robert

##### New Member
Thank you both of you.

I need help tweaking the formulas a bit further though, as this has to happen once for each location for each supplier.

So once for Supplier A, Location A. Once for Supplier A, Location B. Once for Supplier B, Location A and so forth.

So two extra criterias.. I'm sorry that I explained that part poorly. You've both already been tremendously helpfull.

Fluff: So far, your formula sums all budgetary values across all locations and suppliers, but within the specific category and prints it at the first instance of said category.
How do I make it limit itself to the specific location of the specific supplier and then repeat the sum (and print) across each location of each supplier?

Eric W: Your formula sums the budgetary values within each specific category, across the first location, and prints it at the first instance of said category.
How do I make the formula repeat itself once for each location of each supplier?

Thank you both again. You've brought me a huge leap forward already.

#### Fluff

##### MrExcel MVP, Moderator

Book1
ABCDEFGHI
1SupplierLocatonBudget Category ABudget Category BBudget Category CBudgetBudgetCategory A SumBudgetCategory B SumBudget Category C Sum
2Supplier ALocation ACat A ACat B ACat C A20002000135003500
3Supplier ALocation ACat A BCat B ACat C A150012000
4Supplier ALocation ACat A BCat B ACat C B10000  20750
5Supplier ALocation ACat A BCat B BCat C B500 10750
6Supplier ALocation ACat A CCat B BCat C B750010250
7Supplier ALocation ACat A CCat B BCat C B2750
8Supplier BLocation ACat A CCat B BCat C C1800273018001800
9Supplier BLocation ACat A CCat B CCat C D930 1843018631
10Supplier BLocation BCat A DCat B CCat C D2000200020002000
11Supplier BLocation ACat A DCat B CCat C D1750017500
12Supplier BLocation ACat A ECat B DCat C D201201201
13Supplier CLocation ACat A ECat B DCat C D1000345027151000
14Supplier CLocation ACat A ECat B ACat C E735 735735
15Supplier CLocation ACat A ECat B DCat C F1715  1715
16Supplier CLocation BCat A ECat B DCat C G3220322032203172.7
17Supplier CLocation BCat A FCat B ECat C G17.7-47.352.7
18Supplier CLocation BCat A FCat B ECat C G35
19Supplier CLocation BCat A FCat B BCat C G-100 -100
Sheet3
Cell Formulas
RangeFormula
G2:I19G2=IF(COUNTIFS(\$A\$2:\$A2,\$A2,\$B\$2:\$B2,\$B2,C\$2:C2,C2)=1,SUMIFS(\$F:\$F,\$A:\$A,\$A2,\$B:\$B,\$B2,C:C,C2),"")

#### Doge Robert

##### New Member
Book1
ABCDEFGHI
1SupplierLocatonBudget Category ABudget Category BBudget Category CBudgetBudgetCategory A SumBudgetCategory B SumBudget Category C Sum
2Supplier ALocation ACat A ACat B ACat C A20002000135003500
3Supplier ALocation ACat A BCat B ACat C A150012000
4Supplier ALocation ACat A BCat B ACat C B10000  20750
5Supplier ALocation ACat A BCat B BCat C B500 10750
6Supplier ALocation ACat A CCat B BCat C B750010250
7Supplier ALocation ACat A CCat B BCat C B2750
8Supplier BLocation ACat A CCat B BCat C C1800273018001800
9Supplier BLocation ACat A CCat B CCat C D930 1843018631
10Supplier BLocation BCat A DCat B CCat C D2000200020002000
11Supplier BLocation ACat A DCat B CCat C D1750017500
12Supplier BLocation ACat A ECat B DCat C D201201201
13Supplier CLocation ACat A ECat B DCat C D1000345027151000
14Supplier CLocation ACat A ECat B ACat C E735 735735
15Supplier CLocation ACat A ECat B DCat C F1715  1715
16Supplier CLocation BCat A ECat B DCat C G3220322032203172.7
17Supplier CLocation BCat A FCat B ECat C G17.7-47.352.7
18Supplier CLocation BCat A FCat B ECat C G35
19Supplier CLocation BCat A FCat B BCat C G-100 -100
Sheet3
Cell Formulas
RangeFormula
G2:I19G2=IF(COUNTIFS(\$A\$2:\$A2,\$A2,\$B\$2:\$B2,\$B2,C\$2:C2,C2)=1,SUMIFS(\$F:\$F,\$A:\$A,\$A2,\$B:\$B,\$B2,C:C,C2),"")

This works flawlessly. Thank you very much.

Regards,

Robert.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### Eric W

##### MrExcel MVP
Here's the table nomenclature version:

Book1 (version 1).xlsb
CDEFGHIJK
1SupplierLocationBudget Category ABudget Category BBudget Category CBudgetBudget Category A SumBudget Category B SumBudget Category C Sum
2Supplier ALocation ACat A ACat B ACat C A20002000135003500
3Supplier ALocation ACat A BCat B ACat C A150012000
4Supplier ALocation ACat A BCat B ACat C B10000  20750
5Supplier ALocation ACat A BCat B BCat C B500 10750
6Supplier ALocation ACat A CCat B BCat C B750010250
7Supplier ALocation ACat A CCat B BCat C B2750
8Supplier BLocation ACat A CCat B BCat C C1800273018001800
9Supplier BLocation ACat A CCat B CCat C D930 1843018631
10Supplier BLocation BCat A DCat B CCat C D2000200020002000
11Supplier BLocation ACat A DCat B CCat C D1750017500
12Supplier BLocation ACat A ECat B DCat C D201201201
13Supplier CLocation ACat A ECat B DCat C D1000345027151000
14Supplier CLocation ACat A ECat B ACat C E735 735735
15Supplier CLocation ACat A ECat B DCat C F1715  1715
16Supplier CLocation BCat A ECat B DCat C G3220322032203172.7
17Supplier CLocation BCat A FCat B ECat C G17.7-47.352.7
18Supplier CLocation BCat A FCat B ECat C G35
19Supplier CLocation BCat A FCat B BCat C G-100 -100
Sheet39
Cell Formulas
RangeFormula
I2:I19I2=IF(COUNTIFS(INDEX([Supplier],1):[@Supplier],[@Supplier],INDEX([Location],1):[@Location],[@Location],INDEX([Budget Category A],1):[@[Budget Category A]],[@[Budget Category A]])=1,SUMIFS([Budget],[Supplier],[@Supplier],[Location],[@Location],[Budget Category A],[@[Budget Category A]]),"")
J2:K19J2=IF(COUNTIFS(INDEX([Supplier],1):[@Supplier],[@Supplier],INDEX([Location],1):[@Location],[@Location],INDEX(Table2[#Data],1,COLUMN()-COLUMN([Supplier])-3):INDEX(Table2[#Data],ROW()-ROW(INDEX(Table2[#Data],1,1))+1,COLUMN()-COLUMN([Supplier])-3),INDEX(Table2[#Data],ROW()-ROW(INDEX(Table2[#Data],1,1))+1,COLUMN()-COLUMN([Supplier])-3))=1,SUMIFS([Budget],[Supplier],[@Supplier],[Location],[@Location],INDEX(Table2[#Data],0,COLUMN()-COLUMN([Supplier])-3),INDEX(Table2[#Data],ROW()-ROW(INDEX(Table2[#Data],1,1))+1,COLUMN()-COLUMN([Supplier])-3)),"")

I2 has the basic formula, using the appropriate column headers. J2 has a formula that you can copy into I2 and K2 that works in all columns, based on the first column being Supplier, and the Sum columns are 4 columns away from the corresponding category column. As you can see, it's pretty long and complicated, mainly because the table nomenclature doesn't have an easy way to say "this column". I'd stick with the I2 formula, and just change the headings as needed when you copy it to J2:K2.

Of course, Fluff's formula is shorter still, but this does have the benefit of automatically adjusting as you add rows to the table.

Replies
6
Views
738
Replies
0
Views
60
Replies
2
Views
97
Replies
6
Views
109
Replies
3
Views
172

1,130,117
Messages
5,640,207
Members
417,131
Latest member
Seanr19871

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

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