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

[abschy]

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!

 

[etaf]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	country	a
2	project	abc
3	result	55
4			1	2	3	4	5	6	7	8	9	10
5	country	project	data1	data2	data3	data4	data5	data6	data7	data8	data9	data10
6	a	abc	1	2	3	4	5	6	7	8	9	10
7	b	abc	1	2	3	4	5	6	7	8	9	10
8	d	abc	1	2	3	4	5	6	7	8	9	10
9	a	efg	1	2	3	4	5	6	7	8	9	10
10	b	abc	1	2	3	4	5	6	7	8	9	10
11	d	abc	1	2	3	4	5	6	7	8	9	10
12	a	abc	1	2	3	4	5	6	7	8	9	10
13	b	abc	1	2	3	4	5	6	7	8	9	10
14	d	abc	1	2	3	4	5	6	7	8	9	10
15	a	abc	1	2	3	4	5	6	7	8	9	10
16	b	abc	1	2	3	4	5	6	7	8	9	10
17	d	abc	1	2	3	4	5	6	7	8	9	10
Sheet1

Cell Formulas
Range	Formula
B3	B3	=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:L4	C4	=AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)

 

[abschy]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	country	a
2	project	abc
3	result	55
4			1	2	3	4	5	6	7	8	9	10
5	country	project	data1	data2	data3	data4	data5	data6	data7	data8	data9	data10
6	a	abc	1	2	3	4	5	6	7	8	9	10
7	b	abc	1	2	3	4	5	6	7	8	9	10
8	d	abc	1	2	3	4	5	6	7	8	9	10
9	a	efg	1	2	3	4	5	6	7	8	9	10
10	b	abc	1	2	3	4	5	6	7	8	9	10
11	d	abc	1	2	3	4	5	6	7	8	9	10
12	a	abc	1	2	3	4	5	6	7	8	9	10
13	b	abc	1	2	3	4	5	6	7	8	9	10
14	d	abc	1	2	3	4	5	6	7	8	9	10
15	a	abc	1	2	3	4	5	6	7	8	9	10
16	b	abc	1	2	3	4	5	6	7	8	9	10
17	d	abc	1	2	3	4	5	6	7	8	9	10
Sheet1

Cell Formulas
Range	Formula
B3	B3	=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:L4	C4	=AVERAGEIFS(C6:C17,$A$6:$A$17,$B$1,$B$6:$B$17,$B$2)

 

[etaf]

perhaps you could add a count blank to the last average
but if the iferror works then i cant think of much else