countif

[lparlato]

hi! i have 2 separate tabs with the following information in google sheets

	A	B
1	Name	Dates
2	Lauren	09/10/19, 09/11/19, 09/13/19
3	Ron	09/10/19, 09/12/19, 09/14/19
4	Mike	09/10/19

<tbody>
</tbody>

i need a formula for the cells below that have the #1 in them.

=countifs('Tab 1'!$A:$A,$A2,'Tab 1'!$B:$B,B$1)

The only row the formula above is working for is Mike in Row 4 because he only has 1 date. the first part of the formula is to match up the names...then esentially i need the formula to count if any of the dates in TAB 1 - COLUMN B match the date in TAB 2 - ROW 1

	A	B	C	D	E	F
1	Name	09/10/19	09/11/19	09/12/19	09/13/19	09/14/19
2	Lauren	1	1		1
3	Ron	1		1		1
4	Mike	1

<tbody>
</tbody>

 

[DanteAmor]

Re: countif help PLEASEEEEEE

Try this

Tab2
<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: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 >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">10/09/2019</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">11/09/2019</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">12/09/2019</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">13/09/2019</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">14/09/2019</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">14/09/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Lauren</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td > </td><td style="text-align:center; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Ron</td><td style="text-align:center; ">1</td><td > </td><td style="text-align:center; ">1</td><td > </td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mike</td><td style="text-align:center; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </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 >{=IF(-ISNUMBER(FIND(TEXT(B$1,"mm/dd/yy"),TEXT(VLOOKUP($A2,'tab1'!$A$2:$B$4,2,0),"mm/dd/yy"))),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.

 

[lparlato]

Re: countif help PLEASEEEEEE

Try this

Tab2

	A	B	C	D	E	F	G
1	Name	10/09/2019	11/09/2019	12/09/2019	13/09/2019	14/09/2019	14/09/2019
2	Lauren	1	1		1
3	Ron	1		1		1	1
4	Mike	1

<colgroup><col style="font-weight:bold; width:30px; "><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><tbody>
</tbody>

Cell Array Formula B2 {=IF(-ISNUMBER(FIND(TEXT(B$1,"mm/dd/yy"),TEXT(VLOOKUP($A2,'tab1'!$A$2:$B$4,2,0),"mm/dd/yy"))),1,"")} <tbody> </tbody>

<tbody>
</tbody>

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.

thank you thank you!

I need a little more help though. i forgot to mention that on TAB 1, the person may have more than 1 entry.


TAB 1

	A	B
1	Name	Dates
2	Lauren	09/10/19, 9/11/19, 09/13/19
3	Ron	09/10/19, 9/12/19, 09/14/19
4	Mike	09/10/19
5	Lauren	09/10/19
6	Ron	9/12/19
7	Mike	09/10/19, 09/13/19

<tbody>
</tbody>

TAB 2 - should look like this

	A	B	C	D	E	F
	Name	09/10/19	09/11/19	09/12/19	09/13/19	09/14/19
1	Lauren	2	1		1
2	Ron	1		2		1
3	Mike	2			1

<tbody>
</tbody>

Right now the formula is only counting 1 day for Lauren for 09/10/19 even though she has that date listed twice...i want it to count it 2 times.

Also, not sure if this matters, but i am in GOOGLE Sheets and when i try to press control shift enter nothing happens but the formula seems to work with the exception of the case above.

 

[DanteAmor]

Re: countif help PLEASEEEEEE

Try this array formula

{=SUM(IF('tab1'!$A$2:$A$7=$A2,IF(-ISNUMBER(FIND(TEXT(B$1,"mm/dd/yy"),TEXT('tab1'!$B$2:$B$7,"mm/dd/yy"))),1)))}


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.

I have no way to try google sheet, I hope it works for you.