Sum of Average (Multiple Columns) with the same Multiple Criteria/Column

abschy

New Member
Joined
Mar 20, 2019
Messages
25
Hi All!

This is a slightly tricky one..

This is an example of what my data looks like:

1585920435238.png


Database:
Column A: Country
Column B: Project
Column C: Data 1
Column E: Data 2
Column G: Data 3
... so on so forth. I have about 8 other columns of Data to be included


What i need is a formula that does the below:
- Based on the country in C1, and project in C2 --> ie Country A and Project ABC
- Take the average of Data 1 that is of Country A and Project ABC & Round off to nearest hundred --> Roundup(average of C8 and C10,-2)
+ the average of Data 2 that is of Country A and Project ABC & Round off to nearest hundred --> Roundup(average of E8 and E10,-2)
+ the average of Data 3 that is of Country A and Project ABC & Round off to nearest hundred --> Roundup(average of G8 and G10,-2)
+ the same for the other columns

= and the sum of this to be output into C3

I have tried to do it the above way but i get an error that the formula is too long because i'm basically applying the same thing to about 10 columns of data..each with the same 2 criteria..

Any help is greatly appreciated!!

Thank you all!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,751
Office Version
  1. 365
Platform
  1. MacOS
have you thought about perhaps some helper columns - so for each of the 10 columns have an average say row 5 for each data set and then SUM those
What version of Excel do you have
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,751
Office Version
  1. 365
Platform
  1. MacOS
although - seems to work OK, see B3 formula
=SUM(
AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(D6:D17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(E6:E17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(F6:F17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(G6:G17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(H6:H17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(I6:I17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(J6:J17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(K6:K17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(L6:L17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)
)

Book4
ABCDEFGHIJKL
1countrya
2projectabc
3result55
412345678910
5countryprojectdata1data2data3data4data5data6data7data8data9data10
6aabc12345678910
7babc12345678910
8dabc12345678910
9aefg12345678910
10babc12345678910
11dabc12345678910
12aabc12345678910
13babc12345678910
14dabc12345678910
15aabc12345678910
16babc12345678910
17dabc12345678910
Sheet1
Cell Formulas
RangeFormula
B3B3=SUM(AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(D6:D17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(E6:E17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(F6:F17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(G6:G17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(H6:H17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(I6:I17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(J6:J17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(K6:K17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(L6:L17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2))
C4:L4C4=AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)
 

abschy

New Member
Joined
Mar 20, 2019
Messages
25
Hi !

Thanks for your help!

I just edited your formula to the below and it works!
I now have a different issue... there are some cells that are empty and will not give a number.. for example, most of the data in the "Cost PSM52" is empty, which leads to the last line often giving a "#DIV" error..then the whole sum becomes an error..

the only way i could "solve it" was to add the "iferror" to each line and put 0 if false.. do you think there is another way to solve this issue?


=IFERROR(SUM(
ROUNDUP(AVERAGEIFS(Table3[COST PSM],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM10],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM10],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM14],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM14],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM34],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM34],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM40],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM40],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM44],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM44],">0")*[@Size],-2),
ROUNDUP(AVERAGEIFS(Table3[COST PSM52],Table3[Region],[@Region],Table3[BRAND],[@Brand],Table3[COST PSM52],">0")*[@Size],-2)),"No data")


although - seems to work OK, see B3 formula
=SUM(
AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(D6:D17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(E6:E17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(F6:F17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(G6:G17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(H6:H17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(I6:I17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(J6:J17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(K6:K17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),
AVERAGEIFS(L6:L17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)
)

Book4
ABCDEFGHIJKL
1countrya
2projectabc
3result55
412345678910
5countryprojectdata1data2data3data4data5data6data7data8data9data10
6aabc12345678910
7babc12345678910
8dabc12345678910
9aefg12345678910
10babc12345678910
11dabc12345678910
12aabc12345678910
13babc12345678910
14dabc12345678910
15aabc12345678910
16babc12345678910
17dabc12345678910
Sheet1
Cell Formulas
RangeFormula
B3B3=SUM(AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(D6:D17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(E6:E17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(F6:F17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(G6:G17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(H6:H17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(I6:I17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(J6:J17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(K6:K17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2),AVERAGEIFS(L6:L17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2))
C4:L4C4=AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,751
Office Version
  1. 365
Platform
  1. MacOS
perhaps you could add a count blank to the last average
but if the iferror works then i cant think of much else
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,914
Members
418,251
Latest member
aondrla

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