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

Doge Robert

New Member
Joined
Jan 10, 2017
Messages
11
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.

Excel Example.PNG
 

Attachments

  • Excel Example.PNG
    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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
Excel Formula:
=IF(COUNTIFS(C$2:C2,C2)=1,SUMIFS(F:F,C:C,C2),"")
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
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
Joined
Jan 10, 2017
Messages
11
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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
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),"")
 
Solution

Doge Robert

New Member
Joined
Jan 10, 2017
Messages
11
How about
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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
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.
 

Watch MrExcel Video

Forum statistics

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

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
Top