sumproduct snafu
 

The following formula is supposed to return a count of unique values in
column F if 1) column c=c16, and 2) column p=true. It is not returning a
correct value. Any thoughts anybody?

=SUMPRODUCT(--('Jan Database'!$C$2:$C$55200=C16),--('Jan
Database'!$P$2:$P$55200=TRUE),--(('Jan
Database'!$F$2:$F$55200<"")/COUNTIF('Jan Database'!$F$2:$F$55200,'Jan
Database'!$F$2:$F$55200&"")))

Also, why does this darn thing run soooooo sloooooowwwwww? I have a
so-called 'database' filled with about 10,000-15,000 rows of data this
formula is checking.

sumproduct snafu
 

If you download and install the free add-in Morefunc.xll, you can use
the following function...

=COUNTDIFF(IF('Jan Database'!$C$2:$C$10000=C16,IF('Jan
Database'!$P$2:$P$10000=TRUE,IF('Jan Database'!$F$2:$F$10000<"",'Jan
Database'!$F$2:$F$10000))),,FALSE)

The add-in can be found at the following link...

http://xcell05.free.fr/english/index.html

Hope this helps!

In article ,
Coal Miner wrote:

The following formula is supposed to return a count of unique values in
column F if 1) column c=c16, and 2) column p=true. It is not returning a
correct value. Any thoughts anybody?

=SUMPRODUCT(--('Jan Database'!$C$2:$C$55200=C16),--('Jan
Database'!$P$2:$P$55200=TRUE),--(('Jan
Database'!$F$2:$F$55200<"")/COUNTIF('Jan Database'!$F$2:$F$55200,'Jan
Database'!$F$2:$F$55200&"")))

Also, why does this darn thing run soooooo sloooooowwwwww? I have a
so-called 'database' filled with about 10,000-15,000 rows of data this
formula is checking.

sumproduct snafu
 

Great functions. However, the formula is now returning a #ref!. One thought
- I am actually looking for the text string TRUE in Column P. I have the
function isnumber() in each cell in column P (which is obviously returning
TRUE or FALSE). If it is TRUE I want to include this row. Not sure if that
has anything to do with the #ref! result.

"Domenic" wrote:

If you download and install the free add-in Morefunc.xll, you can use
the following function...

=COUNTDIFF(IF('Jan Database'!$C$2:$C$10000=C16,IF('Jan
Database'!$P$2:$P$10000=TRUE,IF('Jan Database'!$F$2:$F$10000<"",'Jan
Database'!$F$2:$F$10000))),,FALSE)

The add-in can be found at the following link...

http://xcell05.free.fr/english/index.html

Hope this helps!

In article ,
Coal Miner wrote:

The following formula is supposed to return a count of unique values in
column F if 1) column c=c16, and 2) column p=true. It is not returning a
correct value. Any thoughts anybody?

=SUMPRODUCT(--('Jan Database'!$C$2:$C$55200=C16),--('Jan
Database'!$P$2:$P$55200=TRUE),--(('Jan
Database'!$F$2:$F$55200<"")/COUNTIF('Jan Database'!$F$2:$F$55200,'Jan
Database'!$F$2:$F$55200&"")))

Also, why does this darn thing run soooooo sloooooowwwwww? I have a
so-called 'database' filled with about 10,000-15,000 rows of data this
formula is checking.

sumproduct snafu
 

If you copied the formula from the post and pasted it into your
worksheet, 'hard returns' have probably been added. Remove them and you
should be okay. Also, TRUE and FALSE values returned by ISNUMBER are
logical values, not text values. So the condition for Column P is fine.

Does this help?

In article ,
Coal Miner wrote:

Great functions. However, the formula is now returning a #ref!. One thought
- I am actually looking for the text string TRUE in Column P. I have the
function isnumber() in each cell in column P (which is obviously returning
TRUE or FALSE). If it is TRUE I want to include this row. Not sure if that
has anything to do with the #ref! result.

sumproduct snafu
 

I fixed the 'hard return' (stupid me!!!). But, the formula is now returning
FALSE. I tried a few things but with no luck, still FALSE. Just so I am
being clear - IF column C of the database = c16 and column P of the database
= true, then I want a count of unique values in column F of the database.
Column F in the database contains various text/numeric strings such as MB323,
AF45, etc. Column C of the database and cell C16 are dates such as 1/3/06.
I tried to remove the dates and replace with a simple text string but this
did not work either. Any ideas this time?

"Domenic" wrote:

If you copied the formula from the post and pasted it into your
worksheet, 'hard returns' have probably been added. Remove them and you
should be okay. Also, TRUE and FALSE values returned by ISNUMBER are
logical values, not text values. So the condition for Column P is fine.

Does this help?

In article ,
Coal Miner wrote:

Great functions. However, the formula is now returning a #ref!. One thought
- I am actually looking for the text string TRUE in Column P. I have the
function isnumber() in each cell in column P (which is obviously returning
TRUE or FALSE). If it is TRUE I want to include this row. Not sure if that
has anything to do with the #ref! result.

sumproduct snafu
 

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

In article ,
Coal Miner wrote:

I fixed the 'hard return' (stupid me!!!). But, the formula is now returning
FALSE. I tried a few things but with no luck, still FALSE. Just so I am
being clear - IF column C of the database = c16 and column P of the database
= true, then I want a count of unique values in column F of the database.
Column F in the database contains various text/numeric strings such as MB323,
AF45, etc. Column C of the database and cell C16 are dates such as 1/3/06.
I tried to remove the dates and replace with a simple text string but this
did not work either. Any ideas this time?

sumproduct snafu
 

My apologies... I just realized that in my first post I neglected to
instruct you to confirm the formula with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

In article ,
Coal Miner wrote:

I fixed the 'hard return' (stupid me!!!). But, the formula is now
returning
FALSE. I tried a few things but with no luck, still FALSE. Just so I am
being clear - IF column C of the database = c16 and column P of the
database
= true, then I want a count of unique values in column F of the database.
Column F in the database contains various text/numeric strings such as
MB323,
AF45, etc. Column C of the database and cell C16 are dates such as 1/3/06.

I tried to remove the dates and replace with a simple text string but this
did not work either. Any ideas this time?

sumproduct snafu
 

Works great! I should have known to confirm with ctrl+shft+enter. Thanks
for your assistance. This function will be very useful in the future.
Thanks again.

By the way - This is MUCH faster than the sumproduct (i'm sure you are aware
of that though).

"Domenic" wrote:

My apologies... I just realized that in my first post I neglected to
instruct you to confirm the formula with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

In article ,
Coal Miner wrote:

I fixed the 'hard return' (stupid me!!!). But, the formula is now
returning
FALSE. I tried a few things but with no luck, still FALSE. Just so I am
being clear - IF column C of the database = c16 and column P of the
database
= true, then I want a count of unique values in column F of the database.
Column F in the database contains various text/numeric strings such as
MB323,
AF45, etc. Column C of the database and cell C16 are dates such as 1/3/06.

I tried to remove the dates and replace with a simple text string but this
did not work either. Any ideas this time?

sumproduct snafu
 

In article ,
Coal Miner wrote:

By the way - This is MUCH faster than the sumproduct (i'm sure you are aware
of that though).

Yes, it's much more efficient. That's why I didn't bother offering a
solution using built-in functions. With a large range such as yours,
using built-in functions would be extremely inefficient...