Eliminating Blank Rows + "COUNTIF"
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&"")) |
Eliminating Blank Rows + "COUNTIF"
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" wrote in message ... 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 help Mike =SUMPRODUCT((A13:A100<"")/COUNTIF(A13:A100,A13:A100&"")) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/2004 |
Eliminating Blank Rows + "COUNTIF"
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" wrote in message ... 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 help Mike =SUMPRODUCT((A13:A100<"")/COUNTIF(A13:A100,A13:A100&"")) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/2004 |
Eliminating Blank Rows + "COUNTIF"
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" wrote in message ... 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 help Mike =SUMPRODUCT((A13:A100<"")/COUNTIF(A13:A100,A13:A100&"")) |
Eliminating Blank Rows + "COUNTIF"
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" wrote in messag .. Hi I am using the formula below which was posted recently, to count th 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&"") -- Outgoing mail is certified Virus Free Checked by AVG anti-virus system (http://www.grisoft.com) Version: 6.0.561 / Virus Database: 353 - Release Date: 13/01/200 |
Eliminating Blank Rows + "COUNTIF"
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" wrote in messag .. Hi I am using the formula below which was posted recently, to count th 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&"") |
All times are GMT +1. The time now is 05:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com