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.

 

[Aladin Akyurek]

What is "to the left of the blank cell"? Column J?

 

[Pat1009]

It can be 4 different things.....
[TABLE="width: 395"]
<tbody>[TR]
[TD][TABLE="width: 395"]
<tbody>[TR]
[TD]DESKTOP SUPPORT - US - INVENTORY MGMT - HOUSTON[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 395"]
<tbody>[TR]
[TD]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - HOU

[TABLE="width: 395"]
<tbody>[TR]
[TD]DESKTOP SUPPORT - US - INVENTORY MGMT - REFRESH - SR[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[TABLE="width: 395"]
<tbody>[TR]
[TD]DESKTOP SUPPORT - US - INVENTORY MGMT - SAN RAMON[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[TABLE="width: 299"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 395"]
<tbody>[TR]
[TD]
[TABLE="width: 395"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[TABLE="width: 395"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

Do these occur in column J of Assets Open, that is, in the column which is on the left of column K that is tested for blank/john*smith?

 

[Pat1009]

yes, But I just noticed that J could also be blank. Maybe we should use Column F
In column F, in that column is....
assigned, pending or resolved

 

[Aladin Akyurek]

yes, But I just noticed that J could also be blank. Maybe we should use Column F
In column F, in that column is....
assigned, pending or resolved

Is this what we are after?

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

 

[Pat1009]

Sorry, that did not work. I worked on this for several hours last night, nothing worked. So I decided to try a simpler approach.
I will now use 2 separate formulas. One in cell U50 and the other in cell V50.

This one in U50 works....

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

This one in V50 does not, it has the wrong total in the cell....

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

 

[Aladin Akyurek]

Try to post a sample and the desired outcome(s) without reference to any formula.

 

[Pat1009]

trying to get mr Excel HTML to work with Excel 2016

 

[Aladin Akyurek]

trying to get mr Excel HTML to work with Excel 2016

Post directly here first:

5 rows from K

Immediately beneath that post

5 rows from K

Following these two, post the value of U3 and V3.

Post also the expected value for the posted sample.