Is Countifs right you use?

[TSing3]

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.

 

[TSing3]

Bump

 

[Fluff]

Maybe something like
=SUMPRODUCT((Sheet1!AX2:BG10="Jack")*(Sheet1!AT2:AT10="Safety"))

 

[DanteAmor]

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]

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]

Which formula are you using?

 

[TSing3]

DanteAmor.....Yours returns a 1 for everything.

 

[TSing3]

Which formula are you using?

First one returned a lot of 6’s
Second returned a 1 for everything.

 

[DanteAmor]

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]

Using the example Dante supplied I get the correct answers for both formulae.

Book1
	A	B	C	D	E	F	G
1		Safety	Quality	FI	Safety	Quality	FI
2	Tsing	3	0	8	3	0	8
3	Jack	5	0	0	5	0	0
4	Damor	2	3	0	2	3	0
Collated Data

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Sheet1!$AX$2:$BG$10=$A2)*(Sheet1!$AT$2:$AT$10=B$1))
E2	{=COUNT(IF(Sheet1!$AT$2:$AT$4=B$1,IF(Sheet1!$AX$2:$BG$4=$A2,1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.