Is Countifs right you use?

TSing3

New Member
Joined
Jan 7, 2015
Messages
42
I’m trying to return the number of times a name appears in a range of cells based on the criteria of another cell. In column AT I have names of departments: Safety, Quality, FI, etc. In columns AX:BG I have a bunch of people’s names.

On another sheet I’m trying to find how many times a name, Jack for example, appears in the table in the same row that Safety appears in AT. I’m basically trying to figure out how many safety projects each person is involved in.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
Maybe something like
=SUMPRODUCT((Sheet1!AX2:BG10="Jack")*(Sheet1!AT2:AT10="Safety"))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
If you have your data like this

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:156.83px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >AT</td><td >AX</td><td >AY</td><td >AZ</td><td >BA</td><td >BB</td><td >BC</td><td >BD</td><td >BE</td><td >BF</td><td >BG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Depts</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name3</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name4</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name5</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name6</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name7</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name8</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name9</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Name10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Safety</td><td >Jack</td><td >Damor</td><td >Tsing</td><td >Jack</td><td >Damor</td><td >Jack</td><td >Jack</td><td >Jack</td><td >Tsing</td><td >Tsing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Quality</td><td > </td><td >Damor</td><td > </td><td > </td><td >Damor</td><td > </td><td > </td><td > </td><td >Damor</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >FI</td><td >Tsing</td><td >Tsing</td><td >Tsing</td><td >Tsing</td><td > </td><td > </td><td >Tsing</td><td >Tsing</td><td >Tsing</td><td >Tsing</td></tr></table>

Try

Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:78.89px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Safety</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Quality</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">FI</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; ">Tsing</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#92d050; ">Jack</td><td style="text-align:right; ">5</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#92d050; ">Damor</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=COUNT(IF(Sheet1!$AT$2:$AT$4=B$1,IF(Sheet1!$AX$2:$BG$4=$A2,1)))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.
 

TSing3

New Member
Joined
Jan 7, 2015
Messages
42
I thought it worked because it returned 6 for someone that has been in 6, but it returns 6 for some people that haven’t been in 6.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
Which formula are you using?
 

TSing3

New Member
Joined
Jan 7, 2015
Messages
42
DanteAmor.....Yours returns a 1 for everything.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
You can give examples of how you have the data.


If your data is as my example, the array formula is enter with shift + control + enter
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
Using the example Dante supplied I get the correct answers for both formulae.

<b></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 /><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><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Safety</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Quality</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FI</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Safety</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Quality</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">FI</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Tsing</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Jack</td><td style="text-align: right;border-left: 1px solid black;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #92D050;;">Damor</td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:10.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)">Collated Data</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)">B2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet1!$AX$2:$BG$10=$A2</font>)*(<font color="Red">Sheet1!$AT$2:$AT$10=B$1</font>)</font>)</td></tr></tbody></table></td></tr></table><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>Array 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)">E2</th><td style="text-align:left">{=COUNT(<font color="Blue">IF(<font color="Red">Sheet1!$AT$2:$AT$4=B$1,IF(<font color="Green">Sheet1!$AX$2:$BG$4=$A2,1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Forum statistics

Threads
1,085,714
Messages
5,385,414
Members
401,943
Latest member
xvpnkr

Some videos you may like

This Week's Hot Topics

Top