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
1	Status	Incident Service Type	Owner Group	Priority	Assigned Group	Assignee	Service CI	CI	Incident Reported Date Time
5288	Assigned	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - HOU		End User Computing: ITC Owned Devices	GIL Laptop PC	01/29/2018 11:49:53 AM
5293	Resolved	User Service Request	GLOBAL IT SERVICE DESK - DISPATCH	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	PC Peripheral	01/29/2018 12:34:20 PM
5337	Pending	User Service Request	GLOBAL IT SERVICE DESK - DISPATCH	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	01/29/2018 3:43:30 PM
5363	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	01/30/2018 1:37:51 AM
5376	Resolved	User Service Request	GLOBAL IT SERVICE DESK	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	01/30/2018 12:07:58 PM
5386	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	End User Computing: ITC Owned Devices	GIL Laptop PC	01/30/2018 2:08:13 PM
5387	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	End User Computing: ITC Owned Devices	GIL Laptop PC	01/30/2018 2:15:02 PM
5454	Pending	User Service Request	DESKTOP SUPPORT - US - SCHEDULING - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	01/31/2018 6:50:09 PM
5455	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	01/31/2018 7:00:32 PM
5482	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	02/01/2018 12:49:01 PM
5484	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	End User Computing: ITC Owned Devices	GIL Desktop PC	02/01/2018 11:13:22 AM
5524	Pending	User Service Request	DESKTOP SUPPORT - US - SCHEDULING - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks		02/01/2018 2:38:32 PM
5530	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	02/01/2018 4:01:59 PM
5532	Resolved	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Low	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	GIL Laptop PC	02/01/2018 7:44:41 PM
5541	Assigned	User Service Request	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR	High	DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - HOU		End User Computing: ITC Owned Devices	GIL Laptop PC	02/01/2018 4:38:49 PM
5547	Pending	User Service Request	GLOBAL IT SERVICE DESK - DISPATCH	Medium	DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON	Sarah Schwan	GIL Devices: Notebooks	PC Peripheral	02/01/2018 6:15:31 PM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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