blakeforums
New Member
- Joined
- Jul 25, 2017
- Messages
- 1
I apologize for the long title. Here is what I am try to do.
[TABLE="width: 800"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Computer Name[/TD]
[TD]Connection Date[/TD]
[TD]Prior Activity Date[/TD]
[TD]Completed Date[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab1[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab2[/TD]
[TD]4/23/2017[/TD]
[TD]4/23/2017[/TD]
[TD]1/18/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab3[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/10/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab4[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/18/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab5[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/23/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab6[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/20/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab7[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]Never Completed[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab8[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab9[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/25/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab10[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]4/24/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab11[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/21/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab12[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]5/25/2017[/TD]
[/TR]
</tbody>[/TABLE]
Essential, I am searching under the company name for a desired company, then I want it to count how many times that company occurs, look at the completed date for the corresponding company if it matches, and then compare the date to Today's date if it is within 5 days, to calculate a percent.
I have a formula that does this for the entire table, but need to break it down by company.
Formula I have for the entire sheet: COUNTIF(INDEX($E:$E,0,MATCH("Completed Date",$E:$E,0)),">="&TODAY()-"5")
Appreciate whatever help I can get.
[TABLE="width: 800"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Computer Name[/TD]
[TD]Connection Date[/TD]
[TD]Prior Activity Date[/TD]
[TD]Completed Date[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab1[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab2[/TD]
[TD]4/23/2017[/TD]
[TD]4/23/2017[/TD]
[TD]1/18/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab3[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/10/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab4[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/18/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab5[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/23/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab6[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/20/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab7[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]Never Completed[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab8[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab9[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/25/2017[/TD]
[/TR]
[TR]
[TD]Cheetah Express[/TD]
[TD]lab10[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]4/24/2017[/TD]
[/TR]
[TR]
[TD]Penguins1[/TD]
[TD]lab11[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]7/21/2017[/TD]
[/TR]
[TR]
[TD]Bears Inc[/TD]
[TD]lab12[/TD]
[TD]7/24/2017[/TD]
[TD]7/24/2017[/TD]
[TD]5/25/2017[/TD]
[/TR]
</tbody>[/TABLE]
Essential, I am searching under the company name for a desired company, then I want it to count how many times that company occurs, look at the completed date for the corresponding company if it matches, and then compare the date to Today's date if it is within 5 days, to calculate a percent.
I have a formula that does this for the entire table, but need to break it down by company.
Formula I have for the entire sheet: COUNTIF(INDEX($E:$E,0,MATCH("Completed Date",$E:$E,0)),">="&TODAY()-"5")
Appreciate whatever help I can get.