Eliminating Blank Rows + "COUNTIF"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am using the formula below which was posted recently, to count the number of rows in the defined range excluding duplication which works great. How would I change this to also exlcude blank rows from the defined range ?

Thanks for your hel

Mik

=SUMPRODUCT((A13:A100<>"")/COUNTIF(A13:A100,A13:A100&""))
 
Hi Mike,

Try:
=SUMPRODUCT((D1:D222<>"")/COUNTIF(D1:D222,D1:D222&""))-SUMPRODUCT((D1:D222=0
)/COUNTIF(D1:D222,D1:D222&""))
to elimitate blank rows and rows with 0s, or
=SUMPRODUCT((D1:D222<>"")/COUNTIF(D1:D222,D1:D222&""))-SUMPRODUCT((D1:D222="
")/COUNTIF(D1:D222,D1:D222&""))
to eliminate only empty (blank) rows.

Cheers


Mike said:
Hi,

I am using the formula below which was posted recently, to count the
Click to expand...
number of rows in the defined range excluding duplication which works great.
How would I change this to also exlcude blank rows from the defined range ?.
 
Oops,

Posted my 'test' version - which used a different range. Yours should be:
=SUMPRODUCT((A13:A100<>"")/COUNTIF(A13:A100,A13:A100&""))-SUMPRODUCT((A13:A1
00=0)/COUNTIF(A13:A100,A13:A100&""))
to elimitate blank rows and rows with 0s, or
=SUMPRODUCT((A13:A100<>"")/COUNTIF(A13:A100,A13:A100&""))-SUMPRODUCT((A13:A1
00="")/COUNTIF(A13:A100,A13:A100&""))
to eliminate only empty (blank) rows.

Cheers


Mike said:
Hi,

I am using the formula below which was posted recently, to count the
Click to expand...
number of rows in the defined range excluding duplication which works great.
How would I change this to also exlcude blank rows from the defined range ?.
 
Mike,

This formula already excludes the blank rows doesn't it? It accommodates the
blank rows, but doesn't count them.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Mike said:
Hi,

I am using the formula below which was posted recently, to count the
Click to expand...
number of rows in the defined range excluding duplication which works great.
How would I change this to also exlcude blank rows from the defined range ?.
 
Hey, thanks, that's great. The problem is the rows I am counting contains values not text which is gving me a minus figure due to the sumproduct function. Is there a way around this problem

Thanks in advanc

Mik

----- macropod wrote: ----

Oops

Posted my 'test' version - which used a different range. Yours should be
=SUMPRODUCT((A13:A100<>"")/COUNTIF(A13:A100,A13:A100&""))-SUMPRODUCT((A13:A
00=0)/COUNTIF(A13:A100,A13:A100&"")
to elimitate blank rows and rows with 0s, o
=SUMPRODUCT((A13:A100<>"")/COUNTIF(A13:A100,A13:A100&""))-SUMPRODUCT((A13:A
00="")/COUNTIF(A13:A100,A13:A100&"")
to eliminate only empty (blank) rows

Cheer


Mike said:
Hi
Click to expand...
number of rows in the defined range excluding duplication which works great
How would I change this to also exlcude blank rows from the defined range ?
 
Yes Bob, Thanks for that. I Figured it out

----- Bob Phillips wrote: ----

Mike

This formula already excludes the blank rows doesn't it? It accommodates th
blank rows, but doesn't count them

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Mike said:
Hi
Click to expand...
number of rows in the defined range excluding duplication which works great
How would I change this to also exlcude blank rows from the defined range ?
 
Back
Top