Complex countifs formula

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
630
Office Version
2016
Platform
Windows
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
Joined
Aug 18, 2011
Messages
6,275
Office Version
365
Platform
Windows
Hi, like this maybe:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B</td><td style="text-align: right;;"></td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:8.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Saturday 2 (2)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIF(<font color="Red">A1:A5,C1:C2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
630
Office Version
2016
Platform
Windows
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
Joined
Aug 18, 2011
Messages
6,275
Office Version
365
Platform
Windows
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:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style="text-align: right;;"></td><td style=";">A,B</td><td style="text-align: right;background-color: #FFFF00;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIF(<font color="Red">$A$1:$A$10,TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">C1,",",REPT(<font color="#FF00FF">" ",99</font>)</font>),ROW(<font color="Teal">INDEX(<font color="#FF00FF">A:A,1</font>):INDEX(<font color="#FF00FF">A:A,LEN(<font color="Navy">C1</font>)-LEN(<font color="Navy">SUBSTITUTE(<font color="Blue">C1,",",""</font>)</font>)+1</font>)</font>)*99-98,99</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
630
Office Version
2016
Platform
Windows
Thanks, this is awesome. I can certainly work with this!
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
630
Office Version
2016
Platform
Windows
Apologies, is it possible to take this one step further.

E.g.

A1A,B6
B11
C1
A1
A1
A1
B1
B2
B2
C1

<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
Joined
Aug 22, 2015
Messages
767
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
C1

<tbody>
</tbody>



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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,275
Office Version
365
Platform
Windows
is it possible to take this one step further.
Hi, not sure if you've already figured it out, but here is one way:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">A,B</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">C</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">C</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIFS(<font color="Red">$B$1:$B$10,D2,$A$1:$A$10,TRIM(<font color="Green">MID(<font color="Purple">SUBSTITUTE(<font color="Teal">D1,",",REPT(<font color="#FF00FF">" ",99</font>)</font>),ROW(<font color="Teal">INDEX(<font color="#FF00FF">A:A,1</font>):INDEX(<font color="#FF00FF">A:A,LEN(<font color="Navy">D1</font>)-LEN(<font color="Navy">SUBSTITUTE(<font color="Blue">D1,",",""</font>)</font>)+1</font>)</font>)*99-98,99</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Nov 23, 2009
Messages
630
Office Version
2016
Platform
Windows
Thanks Aladin - I completely forgot about the named ranges - this works great.

Thanks also FormR and MARZIOTULLI, whose formulas also work.
 

Forum statistics

Threads
1,085,429
Messages
5,383,619
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top