Complex countifs formula

[anglais428]

I am providing a sample table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]A,B[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]A[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]B[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]C[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]A[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]A[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]B[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]B[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]B[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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.