countif

L

lparlato

New Member
Joined
Feb 10, 2016
Messages
31
hi! i have 2 separate tabs with the following information in google sheets

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Lauren[/TD]
[TD]09/10/19, 09/11/19, 09/13/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ron[/TD]
[TD]09/10/19, 09/12/19, 09/14/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mike[/TD]
[TD]09/10/19[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD="align: center"]09/10/19[/TD]
[TD="align: center"]09/11/19[/TD]
[TD="align: center"]09/12/19[/TD]
[TD="align: center"]09/13/19[/TD]
[TD="align: center"]09/14/19[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Lauren[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Ron[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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.
 
Upvote 0
Re: countif help PLEASEEEEEE

DanteAmor said:
Try this

Tab2
ABCDEFG
Lauren
Ron
Mike

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]10/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]11/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]12/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]13/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]14/09/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]14/09/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]1[/TD]

</tbody>

CellArray 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.
Click to expand...





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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Lauren[/TD]
[TD]09/10/19, 9/11/19, 09/13/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ron[/TD]
[TD]09/10/19, 9/12/19, 09/14/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mike[/TD]
[TD]09/10/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lauren[/TD]
[TD]09/10/19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ron[/TD]
[TD]9/12/19[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mike[/TD]
[TD]09/10/19, 09/13/19[/TD]
[/TR]
</tbody>[/TABLE]













TAB 2 - should look like this

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]09/10/19[/TD]
[TD]09/11/19[/TD]
[TD]09/12/19[/TD]
[TD]09/13/19[/TD]
[TD]09/14/19[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Lauren[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Ron[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
82
Peter_SSs
Peter_SSs
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
A
  • Solved
Date format changing ...
Replies
4
Views
196
adambc
A
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top