"" causing issues for COUNTIFS and SUMIFS

[RoundaboutCJP]

I have an issue

I have a column of data A:A
Some cells are 'blank' and some have words in.
The 'blank cells' I believe actually contain "", due to the results of a formula

I wanted to COUNTIF cells weren't blank.
I tried =COUNTA('2016'!A:A), but this doesn't work because of the "" results in the column cells.
I also wanted to do some COUNTIFS and SUMIFS formulas where one of the conditions would be, but I can't because of the "".

How do I work around this?
I thought the following might work for counting the number of cells where A was not blank and B contained "Yes". But this failed.

=SUMPRODUCT(--(LEN('2016'!A:A)>0)*'2016'!B:B="Yes")

Any ideas of the workaround.

Thanks so much.

[AliGW]

Workbook, please, as always. Thanks!

[XOR LX]

Hi,

If the entries in column A are only ever text, the null string ("") or genuine blanks (i.e. never numbers):

=COUNTIF(A:A,"?*")

Regards

[RoundaboutCJP]

Thanks.
I was advised it was best not to attach examples, but to describe as best as possible in the forum as this makes the posts more searchable for future users. Sorry.
Example attached.

[RoundaboutCJP]

Ah ha, this works
=COUNTIF(A:A,"?*")

What does the "?*" mean??

[AliGW]

I was advised it was best not to attach examples, ...

By whom? I think you may have misunderstood. What you will have been advised is not JUST to attach an example, but also to explain in detail in your thread what the issue is and what you are looking for. You may also have been asked not to post an image, but a workbook instead. I hope this clarifies this forum's position for you.

[RoundaboutCJP]

Ah ok, must have been my misunderstanding of what had happened in that thread. Thanks for the advice, most helpful - want to make sure I get etiquette correct as people are SO helpful here.

[RoundaboutCJP]

Any ideas why

This works: =COUNTIFS('2016'!R3:R200,"?*",'2016'!Q3:Q200,"Yes")

BUT this gives an ERROR: =COUNTIFS('2016'!R3:R200,"?*",'2016'!$DD$3:$DN$200,'2016'!DF$2)

[AliGW]

Yes - this:

'2016'!$DD$3:$DN$200

It's not a single-column array.

[Bo_Ry]

Maybe try

=SUMPRODUCT((LEN('2016'!R3:R200)>0)*('2016'!$DD$3:$DN$200='2016'!D$2))

[RoundaboutCJP]

Thanks all