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

#### abschy

##### New Member
Hi All!

This is a slightly tricky one..

This is an example of what my data looks like:

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
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
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
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
perhaps you could add a count blank to the last average
but if the iferror works then i cant think of much else

Replies
2
Views
30
Replies
2
Views
25
Replies
3
Views
242
Replies
0
Views
355
Replies
29
Views
1K

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?

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