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
31,941
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
8,733
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
31,941
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
8,733
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
31,941
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,081,450
Messages
5,358,754
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