SUMPRODUCT/COUNTIF looking at whether a value is in one of several cells in a row

[biomathics23]

Hello and thanks for reading,

I have a setup similar to the following (but much larger), featuring a single column containing names (each cell in this column will have a name), and several columns whose cells may contain letters (though many do not). Each name is unique but more than one letter may appear in a single cell as follows:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Names[/TD]
[TD]Letters1[/TD]
[TD]Letters2[/TD]
[TD]Letters3[/TD]
[TD]Letters4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yanni[/TD]
[TD]A,B[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A, C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Zane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Xerxes[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A, B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yanni[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yanni[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Xerxes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B,C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Zane[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to find a formula that captures the following notion: Given Xerxes and A, how many rows have a "1" in the Numbers column and also have at least one "A" somewhere in Letters1-4 columns? So for Xerxes and A, only row 3 satisfies that, so the formula should return "1". For Yanni and A, rows 1 and 5 work, so the formula should return "2", etc.

I would like to use this formula to then populate a grid like the one below (results shown are based on the above example):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Xerxes[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Yanni[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Zane[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I tried to manipulate the sumproduct and countifs functions in a variety of ways, but I could never figure out how to get it to look at several cells (in actuality 10+) in a single row for a given letter while still summing over all the rows. The only solution I could think of would be to add a helper column of concatenated columns Letters1-4, and then sumproduct or countifs with that helper column and the name column, but I would vastly prefer avoiding such a workaround.

Any thoughts/advice/solutions?

 

[jtakw]

Hi,

I don't see a "Numbers" Column where you say the 1 should be?

 

[biomathics23]

My apologies, I edited the post but apparently missed some parts. That should have been "Names" column and "Xerxes", not "Numbers" and "1". Since it is too late to edit here is how it should have read:



Hello and thanks for reading,

I have a setup similar to the following (but much larger), featuring a single column containing names (each cell in this column will have a name), and several columns whose cells may contain letters (though many do not). Each name is unique but more than one letter may appear in a single cell as follows:


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Names[/TD]
[TD]Letters1[/TD]
[TD]Letters2[/TD]
[TD]Letters3[/TD]
[TD]Letters4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yanni[/TD]
[TD]A,B[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A, C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Zane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Xerxes[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A, B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yanni[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yanni[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Xerxes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B,C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Zane[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am trying to find a formula that captures the following notion: Given Xerxes and A, how many rows have "Xerxes" in the Names column and also have at least one "A" somewhere in Letters1-4 columns? So for Xerxes and A, only row 3 satisfies that, so the formula should return "1". For Yanni and A, rows 1 and 5 work, so the formula should return "2", etc.

I would like to use this formula to then populate a grid like the one below (results shown are based on the above example):

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Xerxes[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Yanni[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Zane[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I tried to manipulate the sumproduct and countifs functions in a variety of ways, but I could never figure out how to get it to look at several cells (in actuality 10+) in a single row for a given letter while still summing over all the rows. The only solution I could think of would be to add a helper column of concatenated columns Letters1-4, and then sumproduct or countifs with that helper column and the name column, but I would vastly prefer avoiding such a workaround.

Any thoughts/advice/solutions?

 

[ExcelElliott]

So this formula does not use sumproduct or countifs, but it works: (This assumes the cell containing the word "Row" is A1)

=MMULT(--(TRANSPOSE($B$2:$B$8=$G13)),N(MMULT(N(ISNUMBER(SEARCH(J$12,$C$2:$F$8))),TRANSPOSE(COLUMN($C$2:$F$8)^0))>0))

Basically it acts like sumproduct on steriods.

Let me know if you have any questions or concerns.

 

[ExcelElliott]

Also, I forgot to mention you must use Control Shift Enter when entering this formula.

 

[jtakw]

My apologies, I edited the post but apparently missed some parts. That should have been "Names" column and "Xerxes", not "Numbers" and "1".

No problem, just needed to know if That was another requirement for the formula(s).

You can either do a SUM/COUNTIFS or a SUMPRODUCT, I'm showing the SUMPRODUCT as the SUM/COUNTIFS is a much longer formula and in case you have additional Letters Columns in the future:

One important note, your sample results seems incorrect.

Book1
	A	B	C	D	E
1	Names	Letters1	Letters2	Letters3	Letters4
2	Yanni	A,B	A		A, C
3	Zane
4	Xerxes		B		A, B
5	Yanni	C			C
6	Yanni			A
7	Xerxes				B,C
8	Zane	A	B	C
9
10
11		A	B	C
12	Xerxes	1	3	1
13	Yanni	4	1	3
14	Zane	1	1	1
Sheet402

Cell Formulas
Range	Formula
B12	=SUMPRODUCT(($A$2:$A$8=$A12)*(ISNUMBER(SEARCH(B$11,$B$2:$E$8))))

B12 formula copied down and across to D14

 

[biomathics23]

ExcelElliott: Steroids indeed! That formula does just what I wanted, though I will have to go through the different functions to try to understand how it actually works. Thanks so much!

jtakw: I tried that approach but got the same results you did, which are not what I wanted: your formula gets 3 for Xerxes and B, because it counts the two B's in row 4 separately. I just wanted to count each row once, regardless of if it had 1 B or multiple B's. I do not think sumproduct can get there (do correct me if I am wrong), but thanks for the suggestion.

 

[Aladin Akyurek]

A bit shorter...

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Names	Letters1	Letters2	Letters3	Letters4				A	B	C
2	Yanni	A,B	A		A, C			Xerxes	1	2	1
3	Zane							Yanni	2	1	2
4	Xerxes		B		A, B			Zane	1	1	1
5	Yanni	C			C
6	Yanni			A
7	Xerxes				B,C
8	Zane	A	B	C
Sheet1

In I2 control+shift+enter, not just enter, copy across, and down:

=SUMPRODUCT(($A$2:$A$8=$H2)+0,(MMULT(ISNUMBER(SEARCH(I$1,$B$2:$E$8))+0,TRANSPOSE(COLUMN($B$2:$E$8)^0))>0)+0)

Note. It's the TRANSPOSE call that necessitates applyin control+shift+enter in this formula with SUMPRODUCT.

 

[jtakw]

jtakw: I tried that approach but got the same results you did, which are not what I wanted: your formula gets 3 for Xerxes and B, because it counts the two B's in row 4 separately. I just wanted to count each row once, regardless of if it had 1 B or multiple B's. I do not think sumproduct can get there (do correct me if I am wrong), but thanks for the suggestion.

Sorry, I misunderstood your question...