countifs NOT empty cells

[ajm]

I am a little confused. Column G in my spreadsheet results from a formula that compares dates. if there is no date in the other column, the formula result is " " or empty.

If i code an autofilter criteria in VBA to select all non blank cells in a column within a table the syntax is:

.ListObjects("CalcPPR").Range.AutoFilter Field:=7, Criteria1:="<>"

The result is 172 rows of data.

BUT when i want to use a formula to count the non blank cells in a range using the same criteria:

=COUNTIFS(G6:G2216,"<>")

the result is the count of all cells in the range (2,210).


And using:
=COUNTIFS(G6:G2216,"< >") ==> there is a single space between < and >,

the result is a count of all cells with nothing in them (except the formula) (2,038).

AND using:
=COUNTIFS(G6:G2216,"<>""")

the result is, again, the count of all cells in the range (2,210)

 

[JoeMo]

=COUNTIFS(G6:G2216,"<>") will count all the cells in range G6:G2216 that have constants or formulas in them, regardless of whether the formulas return "" or not.

If you want to count all cells that are truly blank or have a formula in them that's returning "", try =COUNTIFS(G6:G2216,"")

 

[ajm]

=COUNTIFS(G6:G2216,"<>") will count all the cells in range G6:G2216 that have constants or formulas in them, regardless of whether the formulas return "" or not.

If you want to count all cells that are truly blank or have a formula in them that's returning "", try =COUNTIFS(G6:G2216,"")

I actually want to count all cells in the range that have a value. so in your first sentence, you state

=COUNTIFS(G6:G2216,"<>") will count all the cells in range G6:G2216 that have constants or formulas in them

I want only to count the number of constants that are returned. In this case the answer is 172, which is the number of rows that remain after applying the autofilter as described above.

 

[JoeMo]

I actually want to count all cells in the range that have a value. so in your first sentence, you state


I want only to count the number of constants that are returned. In this case the answer is 172, which is the number of rows that remain after applying the autofilter as described above.

Do all the cells in your range contain formulas?

 

[Oaktree]

COUNTIFS and SUMPRODUCT count the null string returned by a formula differently.

=SUMPRODUCT(--(G6:G2216<>""))

is likely what you're looking for

 

[ajm]

Do all the cells in your range contain formulas?

yes, they do

 

[ajm]

COUNTIFS and SUMPRODUCT count the null string returned by a formula differently.

=SUMPRODUCT(--(G6:G2216<>""))

is likely what you're looking for

This did the job nicely. thanks oaktree.

 

[Alex Blakenburg]

Just for completeness. In MS365 you don't even need the SUMPRODUCT just SUM will do.

=SUM(--(G6:G2216<>""))