Complex countifs formula

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
623
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,212
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
623
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,212
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
623
Thanks, this is awesome. I can certainly work with this!
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
623
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,212
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
623
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,081,454
Messages
5,358,771
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top