In Need of array formula help

[Pat1009]

Hello Board,
I have this formula....

=SUM(COUNTIFS('Assets Open'!$N:$N,">="&U3-6,'Assets Open'!$N:$N,"<="&U3,'Assets Open'!$K:$K,{"john*smith",""}))

It counts in the sheet name Assets Open in column N and cell U3 >= -6 and cell U3 <= it counts all the john smith and any blank cells.
the blank cells need to have something in the cell to the left.
in other words, it will find all the john smith with the criteria above AND all the cells with nothing in it, as long as it has something in the cell to the left of the blank cell.

 

[Pat1009]

Awesome, I was able to get Mr Excel HTML working. So I can go back to the original request. How to Count both Sarah Schwan and the blank. (I used the real name).

	F	G	H	I	J	K	L	M	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Status[/TD]
[TD="bgcolor: #666666"]Incident Service Type[/TD]
[TD="bgcolor: #666666"]Owner Group[/TD]
[TD="bgcolor: #666666"]Priority[/TD]
[TD="bgcolor: #666666"]Assigned Group[/TD]
[TD="bgcolor: #FFFF00"]Assignee[/TD]
[TD="bgcolor: #666666"]Service CI[/TD]
[TD="bgcolor: #666666"]CI[/TD]
[TD="bgcolor: #FFFF00"]Incident Reported Date Time[/TD]

[TD="align: center"]5288[/TD]
[TD="bgcolor: #FFFF00"]Assigned[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[TD="bgcolor: #F0F0F4"]Medium[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - HOU[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #F0F0F4"]End User Computing: ITC Owned Devices[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/29/2018 11:49:53 AM[/TD]

[TD="align: center"]5293[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]GLOBAL IT SERVICE DESK - DISPATCH[/TD]
[TD="bgcolor: #FFFFFF"]Medium[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #FFFFFF"]PC Peripheral[/TD]
[TD="bgcolor: #FFFF00"]01/29/2018 12:34:20 PM[/TD]

[TD="align: center"]5337[/TD]
[TD="bgcolor: #FFFF00"]Pending[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]GLOBAL IT SERVICE DESK - DISPATCH[/TD]
[TD="bgcolor: #FFFFFF"]Medium[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #FFFFFF"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/29/2018 3:43:30 PM[/TD]

[TD="align: center"]5363[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFFFF"]Low[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #FFFFFF"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/30/2018 1:37:51 AM[/TD]

[TD="align: center"]5376[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]GLOBAL IT SERVICE DESK[/TD]
[TD="bgcolor: #F0F0F4"]Medium[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/30/2018 12:07:58 PM[/TD]

[TD="align: center"]5386[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[TD="bgcolor: #F0F0F4"]Medium[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]End User Computing: ITC Owned Devices[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/30/2018 2:08:13 PM[/TD]

[TD="align: center"]5387[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[TD="bgcolor: #FFFFFF"]Medium[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]End User Computing: ITC Owned Devices[/TD]
[TD="bgcolor: #FFFFFF"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/30/2018 2:15:02 PM[/TD]

[TD="align: center"]5454[/TD]
[TD="bgcolor: #FFFF00"]Pending[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - SCHEDULING - SAN RAMON[/TD]
[TD="bgcolor: #F0F0F4"]Low[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/31/2018 6:50:09 PM[/TD]

[TD="align: center"]5455[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFFFF"]Low[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #FFFFFF"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]01/31/2018 7:00:32 PM[/TD]

[TD="align: center"]5482[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #F0F0F4"]Low[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 12:49:01 PM[/TD]

[TD="align: center"]5484[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[TD="bgcolor: #F0F0F4"]Medium[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]End User Computing: ITC Owned Devices[/TD]
[TD="bgcolor: #F0F0F4"]GIL Desktop PC[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 11:13:22 AM[/TD]

[TD="align: center"]5524[/TD]
[TD="bgcolor: #FFFF00"]Pending[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - SCHEDULING - SAN RAMON[/TD]
[TD="bgcolor: #F0F0F4"]Low[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"][/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 2:38:32 PM[/TD]

[TD="align: center"]5530[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #F0F0F4"]Low[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 4:01:59 PM[/TD]

[TD="align: center"]5532[/TD]
[TD="bgcolor: #FFFF00"]Resolved[/TD]
[TD="bgcolor: #F0F0F4"]User Service Request[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #F0F0F4"]Low[/TD]
[TD="bgcolor: #F0F0F4"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #F0F0F4"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #F0F0F4"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 7:44:41 PM[/TD]

[TD="align: center"]5541[/TD]
[TD="bgcolor: #FFFF00"]Assigned[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[TD="bgcolor: #FFFFFF"]High[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - HOU[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFFFF"]End User Computing: ITC Owned Devices[/TD]
[TD="bgcolor: #FFFFFF"]GIL Laptop PC[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 4:38:49 PM[/TD]

[TD="align: center"]5547[/TD]
[TD="bgcolor: #FFFF00"]Pending[/TD]
[TD="bgcolor: #FFFFFF"]User Service Request[/TD]
[TD="bgcolor: #FFFFFF"]GLOBAL IT SERVICE DESK - DISPATCH[/TD]
[TD="bgcolor: #FFFFFF"]Medium[/TD]
[TD="bgcolor: #FFFFFF"]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[TD="bgcolor: #FFFF00"]Sarah Schwan[/TD]
[TD="bgcolor: #FFFFFF"]GIL Devices: Notebooks[/TD]
[TD="bgcolor: #FFFFFF"]PC Peripheral[/TD]
[TD="bgcolor: #FFFF00"]02/01/2018 6:15:31 PM[/TD]

</tbody>

Assets Open​

Using this formula, the output is 14 in cell U49.

=COUNTIFS('Assets Open'!$N:$N,">="&U3 -6,'Assets Open'!$N:$N,"<="&U3 +1,'Assets Open'!$K:$K,"sarah*schwan")

Using this formula, the out put is 31 in Cell V49.

=COUNTIFS('Assets Open'!$N:$N,">="&V3 -6,'Assets Open'!$N:$N,"<="&V3 +1,'Assets Open'!$K:$K,"sarah*schwan")

The U3-6 and U3+1 is the part that does not seem to work. (they are looking at weekending dates on the same sheet as the output.
The output in Cell U49 should be 16. And the output in Cell V49 should be 46. But the proof to that is not showing here.

 

[Aladin Akyurek]

Forget about the formulas you have. Just state what are the expected result(s) for this sample?

 

[Pat1009]

The output in Cell U49 should be 16.

 

[Pat1009]

Not sure if this will help. This is the formulas on the sheet that the results of the countifs are on....

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AJ</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Tech Name</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Break/Fix Cases Resolved</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Planned Event Cases InvMgmt Resolved  </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Total Cases Resolved</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Break/Fix Cases Open</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Planned Event Cases InvMgmt  Open </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Total Cases Open</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">20 Bus Days </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">Final Monthly Rolling CPD Avg</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #F4FFB1;;">Available Utilization %</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #CCC0DA;;">Paid Utilization %</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #8DB4E2;;">Activities Per Day</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-left: 1px solid black;background-color: #C0C0C0;;">Location</td><td style="text-align: center;border-top: 1px solid black;;">1/26</td><td style="text-align: center;border-top: 1px solid black;;">2/2</td><td style="text-align: center;border-top: 1px solid black;;">2/9</td><td style="text-align: center;border-top: 1px solid black;;">2/16</td><td style="text-align: center;border-top: 1px solid black;;">1/26</td><td style="text-align: center;border-top: 1px solid black;;">2/2</td><td style="text-align: center;border-top: 1px solid black;;">2/9</td><td style="text-align: center;border-top: 1px solid black;;">2/16</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">1/26</td><td style="text-align: center;border-top: 1px solid black;;">2/2</td><td style="text-align: center;border-top: 1px solid black;;">2/9</td><td style="text-align: center;border-top: 1px solid black;;">2/16</td><td style="text-align: center;border-top: 1px solid black;;">1/26</td><td style="text-align: center;border-top: 1px solid black;;">2/2</td><td style="text-align: center;border-top: 1px solid black;;">2/9</td><td style="text-align: center;border-top: 1px solid black;;">2/16</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #C0C0C0;;">Bus Days Worked</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #F4FFB1;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #CCC0DA;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #8DB4E2;;"></td></tr></tbody></table><p style="width:12em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Chevron EUE MS </p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=D3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=E3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=F3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=TODAY(<font color="Blue"></font>)-WEEKDAY(<font color="Blue">TODAY(<font color="Red"></font>)</font>)-1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=C3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=D3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=E3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">=F3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P3</th><td style="text-align:left">=Q3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q3</th><td style="text-align:left">=R3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R3</th><td style="text-align:left">=S3-7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">S3</th><td style="text-align:left">=N3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T3</th><td style="text-align:left">=P3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">U3</th><td style="text-align:left">=Q3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V3</th><td style="text-align:left">=R3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W3</th><td style="text-align:left">=S3</td></tr></tbody></table></td></tr></table><br />


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AJ</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">49</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sarah Schwan</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">San Ramon Inv (Sched)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">29</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">68</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">31</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">126</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">18</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFCC99;;">3.8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">90%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCC0DA;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #8DB4E2;;">7.0</td></tr></tbody></table><p style="width:12em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Chevron EUE MS </p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Closed'!$N:$N,">="&G3 -6,'Assets Closed'!$N:$N,"<="&G3 +1,'Assets Closed'!$M:$M,"Sarah*Schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Closed'!$N:$N,">="&H3 -6,'Assets Closed'!$N:$N,"<="&H3 +1,'Assets Closed'!$M:$M,"Sarah*Schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Closed'!$N:$N,">="&I3 -6,'Assets Closed'!$N:$N,"<="&I3 +1,'Assets Closed'!$M:$M,"Sarah*Schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Closed'!$N:$N,">="&J3 -6,'Assets Closed'!$N:$N,"<="&J3 +1,'Assets Closed'!$M:$M,"Sarah*Schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O49</th><td style="text-align:left">=SUM(<font color="Blue">C49:N49</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Open'!$N:$N,">="&T3 -6,'Assets Open'!$N:$N,"<="&T3 +1,'Assets Open'!$K:$K,"sarah*schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">U49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Open'!$N:$N,">="&U3 -6,'Assets Open'!$N:$N,"<="&U3 +1,'Assets Open'!$K:$K,"sarah*schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Open'!$N:$N,">="&V3 -6,'Assets Open'!$N:$N,"<="&V3 +1,'Assets Open'!$K:$K,"sarah*schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W49</th><td style="text-align:left">=COUNTIFS(<font color="Blue">'Assets Open'!$N:$N,">="&W3 -6,'Assets Open'!$N:$N,"<="&W3 +1,'Assets Open'!$K:$K,"sarah*schwan"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AD49</th><td style="text-align:left">=O49/AC49</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AE49</th><td style="text-align:left">=AC49/20</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AJ49</th><td style="text-align:left">=SUM(<font color="Blue">AB49/AC49</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Pat1009]

Aladin,
Were you able to come up with the formula? if not I understand.

 

[Aladin Akyurek]

Aladin,
Were you able to come up with the formula? if not I understand.

Care to follow literally my request in post #10 ? Please also remove the irrelevant data when creating the requested sample.

 

[Pat1009]

It worked with this...

=SUM(COUNTIFS('Assets Open'!$N:$N,">="&W3 -6,'Assets Open'!$N:$N,"<="&W3 +1,'Assets Open'!$K:$K,{"john*smith";""}))

Lets see if it works this Monday when I do the next report. Thank you for your help Aladin