sumproduct snafu

[Coal Miner]

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.

 

[Domenic]

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!

 

[Coal Miner]

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]

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?

 

[Coal Miner]

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]

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

 

[Domenic]

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

 

[Coal Miner]

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]

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