Counting the Range of Cells by comparing its values with the values of another range

[gnaga]

Hi All,

I have a two range of cells having an alphabet in each cell (R, M, N, D...)

For ex. Range 1 A10:A41 will have single Alphabet and Range 2 B10:B41 will have single or more than one alphabet (MC, MP,N,R...)

In B9 I need a formula to count number of each unique alphabet in Range 2 for the following condition

Condition
If first cell in Range 1 having R and first cell in Range 2 having any alphabet other than R then take the value as say 12
If Second cell in Range 1 having R and second cell in Range 2 having any alphabet other than R then take the value as say 12 add to the previous 12 and so on until the last cell. Display the final count in B9.

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U
6
7		R	M	M	D	N	R	R	N	N	R	Range 1
8	32	M	M	M	D	N	R	M	N	N	C	Range 2
9
10		12	0	0	0	0	0	12	0	0	8
Sheet1

Cell Formulas
Range	Formula
J8	J8	=SUM(K10:T10)

 

[gnaga]

I need a formula to get the value in J8..

 

[gnaga]

Hi. All

Sorry to very wrongly phrase my question in a hurry. I understand there is no clarity in my previous post on what I wanted. Please find the below part.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Row 1
2		R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	N	R	R	R	R	M	M	N	Row 2
3		N			C	M	M	N	N			M		M	M	N	N					M	M	N	N				M	M	M	N	Row 3
4	44	12	0	0	8	0	0	0	0	0	0	12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	12	0	0	0	Row 4
Sheet1

Cell Formulas
Range	Formula
C1:AF1	C1	=B1+1
A4	A4	=SUM(B4:AF4)
B4:AF4	B4	=IF(AND(B2="R",OR(B3="M",B3="N")),12,IF(AND(B2="R",B3="C"),8,0))

Size of the Range for Row 2 and Row 3 will always be same that is 31 entries.
Range Row 3 will have some missing entry while the respective cell in Row 2 is "R"
While the Row 2 cell is "R" and the Row 3 cell is either "M" or "N" value 12 to be taken for meeting this condition.
While the Row 2 cell is "R" and the Row 3 cell is either "C" value 8 to be taken for meeting this condition.
In future these condition may change with different Alphabet in Row 3 and different value while Row 3 cell is "R"

Finally all the values to be added in a cell for which I need a single formula.

Hope I explain my question reasonably clear.

 

[gnaga]

Any clue on this? Probably combination of Index and Match Array Function will do the job. But I need some guidance on how to use them for this specific purpose.

 

[Fluff]

What's wrong with the formulae you've got?

 

[gnaga]

I entered the formula in Row 4 only for explaining what I wanted. I want to bring this result in a single cell (A4) by eliminating the Row 4. Because I have many more other rows of data similar to Row 2 and Row 3 to compare and bring the their result in the corresponding single cell.

I shown here as sample data what the result I wanted.

 

[Fluff]

Ok, how about

=SUM(COUNTIFS(B2:AG2,"R",B3:AG3,{"M","N"}))*12+COUNTIFS(B2:AG2,"R",B3:AG3,"C")*8

 

[gnaga]

Thanks Fluff, I will try this and let you know.

 

[Fluff]

 

[gnaga]

Fluff, It works fine. Could you please explain what the curly bracket does in the CountIfs function?