# Complex countifs formula

#### anglais428

##### Well-known Member
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

##### MrExcel MVP
Hi, like this maybe:

#### anglais428

##### Well-known Member
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

##### MrExcel MVP
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:

Last edited:

#### anglais428

##### Well-known Member
Thanks, this is awesome. I can certainly work with this!

#### anglais428

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
is it possible to take this one step further.
Hi, not sure if you've already figured it out, but here is one way:

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

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

you can just have:

=SUMPRODUCT(COUNTIFS,List1))

#### anglais428

##### Well-known Member
Thanks Aladin - I completely forgot about the named ranges - this works great.

Thanks also FormR and MARZIOTULLI, whose formulas also work.