COUNTIFS Is Not Blank not Working with Query Data Pull

[cp5545]

Hey everyone. New to this particular board, but hoping to get help.

I am trying to run a COUNTIFS statement that I've used for years. There is a particular column that if blank, I want to ignore it in the COUNTIFS statement which is one of the range/criteria entries as:

'AZURE PowerBI Support Team RPT'!$B:$B,"<>")

That works fine if I have a sheet that has data copy/pasted in it. But using Excel data source query against an azure sas blob URL I generated, it isn't able to figure out what is blank and what isn't using that same formula. But if I go into the sheet that the query injects into and drop down the filter for that column.. the option "blank" is there and I can filter it down.

So to summarize... using the "<>" to check against a field (if it's blank) in a COUNTIFS doesn't seem to work if the data is coming from a query.

To compensate, I used the advanced query editor and added a conditional column that uses ISBLANK logic and then populates a value that I can use in my COUNTIFS. But I was curious if anyone else has run into this or not.

 

[richh]

Hi cp5545,

Have you tried using the AGGREGATE function to perform the task? Really powerful formula that will skip blanks/errors/etc.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Just out of curiosity, see if this works...
=COUNTIF($B:$B, "><" )

M.

 

[cp5545]

Hi cp5545,

Have you tried using the AGGREGATE function to perform the task? Really powerful formula that will skip blanks/errors/etc.

Thanks for the quick reply. Given the multi-conditional check I don't believe AGGREGATE works. Admittedly I haven't tried re-writing the rows to account for it, but this is what the full COUNTIFS formula looks like (using my conditional column from the query editor):

=COUNTIFS('AZURE PowerBI Support Team RPT'!$N:$N,">="&'Flow Management Analysis'!HA$1,'AZURE PowerBI Support Team RPT'!$N:$N,"<"&SUM('Flow Management Analysis'!HA$1+1),'AZURE PowerBI Support Team RPT'!$I:$I,"*Closed*",'AZURE PowerBI Support Team RPT'!$S:$S,"<>UNASSIGNED")

It is essentially performing the following:

• Is the date applicable to the data set within that column's designated date range
• Is in a status of closed (multiple closed options)
• Assigned to isn't blank (which in this case, looks to that conditional column that shows "UNASSIGNED" if the script determines it's blank)

I wasn't able to discern a way that AGGREGATE could do those conditional checks. If I am wrong there please let me know, I'll dive deeper if so

 

[cp5545]

Welcome to Mr Excel forum

Just out of curiosity, see if this works...
=COUNTIF($B:$B, "><" )

M.

Oh wow. That worked. Never even thought of that. What exactly is that doing versus the "not equal to" that I was trying?

 

[Marcelo Branco]

Oh wow. That worked. Never even thought of that. What exactly is that doing versus the "not equal to" that I was trying?

I know this is weird! Don't ask me why it works with >< ?

I do not know why (???)

M.

 

[cp5545]

I know this is weird! Don't ask me why it works with >< ?

I do not know why (???)

M.

Yeah really odd. <> works with raw data in a sheet that I compare against. >< doesn't work when I tested it with raw copy/paste into a sheet (removing the query). But when I load data through the query it inverses and <> fails while >< works. I'll be adding that to my list of crazy workarounds.

Really appreciate it.

 

[Marcelo Branco]

You're welcome. Glad to help

M.