Second search criteria

[Pat1009]

Hello board,
I have this formula....

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

It works great. But I want to add another criteria in the same column K:K
So it will search for a blank cell in addition to john smith.... count them both together.

 

[magpie2000k]

Re: Need help with a second search criteria

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

does that work? replace bob smith as see fit hard to replcate without knowing what is in the other fields

Or

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

 

[Pat1009]

Re: Need help with a second search criteria

The top one look like it could work, but instead of bob smith, I need it to find all blank cells with something in the cell next to it. left or right of the blank cell

 

[magpie2000k]

Re: Need help with a second search criteria

You lost me can you show a sample of data? and all columns so I can understand

one of the more knowledgeable might know better than me im no expert

Are you saying you want to see John Smith in K and blanks in K

 

[Pat1009]

Re: Need help with a second search criteria

yes, that is what I am saying.
but when it looks for the blanks, the blanks must have some/any data to the left or right cell.
the reason is I don't want it to find blanks cells all the way to the bottom of the spreadsheet. just the blanks with data in the cell next to the blank.

 

[magpie2000k]

Re: Need help with a second search criteria

So you do not need the John smith part only to match if K is K is blank and J or L are not blank.

Sorry I dont get what you are saying

Surely one column will always have some data in it and therfore you use that to stop it going thousands of rows

 

[Pat1009]

Re: Need help with a second search criteria

This one looks like it would work with 1 change....
=COUNTIFS('Assets Open'!$N:$N,">="&W3 -6,'Assets Open'!$N:$N,"<="&W3 +1,'Assets Open'!$K:$K,{"john*smith","BLANK CELL"})
The stuff in red is what needs to be changed. I want it to look for all the john smith and all the blank cells and count them all.

 

[Aladin Akyurek]

Re: Need help with a second search criteria

Is this what you are after?

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

 

[Pat1009]

Re: Need help with a second search criteria

that helped a lot, but for some reason it is counting 28 when there are only 26, I will have to find out why.

 

[Aladin Akyurek]

Re: Need help with a second search criteria

that helped a lot, but for some reason it is counting 28 when there are only 26, I will have to find out why.

Care to post a scaled-down sample of the data where miscount occurs?