Sum Based on Multiple Criteria

[kumara_faith]

Hi,

I have the following raw data from an AS400 system:

Raw Data in Sheet 1

Each of the process type is categorized as follows:

Table 1 In Sheet 2

The above category does not exist in the raw data.

Would it be possible to summarize the raw data in a summary table in sheet 3 by referencing the raw data in sheet 1 to the category in sheet 2 ?

Summary Table in Sheet 3

 

[Eric W]

Here are a couple of ways, depending on which version of Excel you have:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I
1	Process Type	Pages		Process Type			Category Type	Total Pages
2	FGTY	10		FGTY	Foreign		Foreign	39	39
3	FGTR	10		FGTR	Foreign		Transfer	40	40
4	FDEW	9		FDEW	Foreign		Yield	31	31
5	FRWS	10		FRWS	Foreign
6	TREW	10		TREW	Transfer
7	TGVB	10		TGVB	Transfer
8	TRFS	10		TRFS	Transfer
9	TYHJ	10		TYHJ	Transfer
10	YTBB	11		YTBB	Yield
11	YUIO	10		YUIO	Yield
12	YHNM	10		YHNM	Yield
Sheet5

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT($B$2:$B$12,--(COUNTIFS($D$2:$D$12,$A$2:$A$12,$E$2:$E$12,G2)>0))
I2:I4	I2	=SUM(SUMIF($A$2:$A$12,IF($E$2:$E$12=G2,$D$2:$D$12,"xxxx"),$B$2:$B$12))
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 365 with the new array functions, this also should work:

=SUM(SUMIF($A$2:$A$12,FILTER($D$2:$D$12,$E$2:$E$12=G2),$B$2:$B$12))

 

[kumara_faith]

Thank you Eric W. That worked. Appreciate your time and patience.

 

[Peter_SSs]

That worked.

I wonder which one you used and whether or not the last suggestion was applicable for you?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another Excel 365 option if you have the new array functions is ..

20 10 10.xlsm
	A	B	C	D	E	F	G	H
1	Process Type	Pages		Process Type			Category Type	Total Pages
2	FGTY	10		FGTY	Foreign		Foreign	39
3	FGTR	10		FGTR	Foreign		Transfer	40
4	FDEW	9		FDEW	Foreign		Yield	31
5	FRWS	10		FRWS	Foreign
6	TREW	10		TREW	Transfer
7	TGVB	10		TGVB	Transfer
8	TRFS	10		TRFS	Transfer
9	TYHJ	10		TYHJ	Transfer
10	YTBB	11		YTBB	Yield
11	YUIO	10		YUIO	Yield
12	YHNM	10		YHNM	Yield
Sum

Cell Formulas
Range	Formula
H2:H4	H2	=SUM(FILTER(B$2:B$12,VLOOKUP(A$2:A$12,D$2:E$12,2,0)=G2,0))

 

[kumara_faith]

Hi Peter,

Thank you for your feedback. I have updated my account details. Yes, I am using Excel 365 and the last suggestion worked for me.

 

[Peter_SSs]

Thanks for the additional information - & for updating your profile.