Complex countifs formula

[anglais428]

I am providing a sample table below:

A

B

A

A

B

<tbody>
</tbody>

I can do a Countif to get the number of A's or B's. Or something like =SUM(COUNTIF($A$2:$A$7,{"A","B"})) to get the number of As and Bs.
I would like to refer to the part {"A","B"} as a cell reference, rather than have to manually type this.

Note, this is a condensed version for illustration only (so sumproduct would not be the best result for me).

Thanks

 

[FormR]

Hi, like this maybe:

Excel 2013/2016
	A	B	C	D	E
1	A		A		5
2	B		B
3	A
4	A
5	B
Saturday 2 (2)

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(COUNTIF(A1:A5,C1:C2))

 

[anglais428]

Thanks FormR,
I am trying to avoid this type of formula (if possible) as there are multiple items inside one cell (e.g. {"milk","juice","tea","coffee"}) and it would be preferable (if possible) to keep them listed like this in the one cell rather than splitting them into a single cell each.

 

[FormR]

Hi, it is a little more complex but if you can set it up by not including the {} and "" characters as below then you can try:

Excel 2013/2016
	A	B	C	D
1	A		A,B	8
2	B
3	A
4	A
5	B
6	C
7	A
8	A
9	B
10	C
Sheet1

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(COUNTIF($A$1:$A$10,TRIM(MID(SUBSTITUTE(C1,",",REPT(" ",99)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1))*99-98,99))))

 

[anglais428]

Thanks, this is awesome. I can certainly work with this!

 

[anglais428]

Apologies, is it possible to take this one step further.

E.g.

A	1		A,B	6
B	1		1
C	1
A	1
A	1
A	1
B	1
B	2
B	2
C	1

<tbody>
</tbody>

So the answer would be 6 based on finding A and B in the first column and where "1" is beside each A or B in the second column.

Thanks

 

[MARZIOTULLIO]

if you change your file in this way

	A​	B​	C​	D​	E​
1​	A	1​		a	6​
2​	B	1​		b
3​	C	1​		1​
4​	A	1​
5​	A	1​
6​	A	1​
7​	B	1​
8​	B	2​
9​	B	2​
10​	C	1

<tbody>
</tbody>

e1=SUMPRODUCT(COUNTIFS(A1:A9,D1:D2,B1:B9,B1))

 

[FormR]

is it possible to take this one step further.

Hi, not sure if you've already figured it out, but here is one way:

Excel 2013/2016
	A	B	C	D	E
1	A	1		A,B	6
2	B	1		1
3	C	1
4	A	1
5	A	1
6	A	1
7	B	1
8	B	2
9	B	2
10	C	1
Sheet1

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(COUNTIFS($B$1:$B$10,D2,$A$1:$A$10,TRIM(MID(SUBSTITUTE(D1,",",REPT(" ",99)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(D1)-LEN(SUBSTITUTE(D1,",",""))+1))*99-98,99))))

 

[Aladin Akyurek]

Also, if you define List1 in the Name Manager as referring to:

={"milk","juice","tea","coffee"}

you can just have:

=SUMPRODUCT(COUNTIFS,List1))

 

[anglais428]

Thanks Aladin - I completely forgot about the named ranges - this works great.

Thanks also FormR and MARZIOTULLI, whose formulas also work.