Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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&"")) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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&"")) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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&"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered | Excel Worksheet Functions | |||
"Last Cell" to find last row, but can't delete blank rows | Excel Discussion (Misc queries) | |||
Pivot Tables - "(blank)" in rows and cells | Excel Discussion (Misc queries) | |||
blank rows still "read" by excel | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |