count and sum up based on specific conditions

[sblad3]

Hi All, Seek your help on below

Based on the bandwidth 2 or 4, I need to count specific character on Q2 row, e.g.
Count of "E" in Q2 row,
bandwidth=2, total "E"= 2
bandwidth=4, total "E"= 2

Count of "D" in Q2 row,
bandwidth=2, total "D"= 2
bandwidth=4, total "D"= 0

Bandwidth	2	4	4	4	4	2	2	4
Q1	B	A	A	B	A	B	A	A
Q2	DE	C	BE	E		CD	EF

 

[Peter_SSs]

I suggest that you update your Account details (or 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’)

Try this

23 06 15.xlsm
	A	B	C	D	E	F	G	H	I
1	Bandwidth	2	4	4	4	4	2	2	4
2	Q1	B	A	A	B	A	B	A	A
3	Q2	DE	C	BE	E		CD	EF
4
5	Row	Q2	Q2	Q2	Q2	Q1
6	Characters	E	E	D	D	A
7	Bandwidth	2	4	2	4	4
8	Total	2	2	2	0	4
Count

Cell Formulas
Range	Formula
B8:F8	B8	=SUMPRODUCT(($A2:$A3=B5)*($B1:$I1=B7)*(ISNUMBER(FIND(B6,$B2:$I3))))