Eliminating Blank Rows + "COUNTIF"

M

Mike

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&""))
 
M

macropod

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 ?.
 
M

macropod

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 ?.
 
B

Bob Phillips

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 ?.
 
M

Mike

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 ?
 
M

Mike

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 ?
 
Top