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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
126
Peter_SSs
Peter_SSs
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
A
  • Solved
Date format changing ...
Replies
4
Views
220
adambc
A
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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