[mso] Re: Count Non-Blank Cells in Excel 2002
- From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2007 20:33:27 +1000
Ray
Thanks for the thanks. But ...
Original it isn't. I first learnt about SumProduct from one of the many
Excel lists around (probably a Yahoo group). Since then, I've used it a
lot.
Glad to help create a few more converts. :-)
Regards, Dave S
----- Original Message -----
From: "Ray Shapp" <ras45@xxxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2007 6:49 PM
Subject: [mso] Re: Count Non-Blank Cells in Excel 2002
> Hi Dave,
>
> I asked the original question in this thread, but someone else asked you
> to
> explain how your formula worked. I'm glad he did, and I appreciate your
> explanation because it confirmed what I had surmised. I would not have
> recognized why you can't use the FIND("abc",A1:A9) separately without your
> post.
>
> Thank you for your original solution and also for the follow-up.
>
> Ray Shapp
>
> ----- Original Message -----
> From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
> To: <mso@xxxxxxxxxxxxx>
> Sent: Monday, July 09, 2007 7:18 AM
> Subject: [mso] Re: Count Non-Blank Cells in Excel 2002
>
>
>> FIND("abc",A1:A9)
>>
>> This is the Find function, but with a range of cells so that it can be
>> used
>> with SumProduct. It will return >0 for each cell if "abc" is found or
>> #value if not found.
>>
>> ISERROR(FIND("abc",A1:A9))
>>
>> True if not found, false if found.
>>
>> NOT(ISERROR(FIND("abc",A1:A9)))
>>
>> True if found, false if not found.
>>
>> (NOT(ISERROR(FIND("abc",A1:A9))))
>>
>> The extra pair of parentheses required by SumProduct.
>>
>> *1
>>
>> A multiplication operation (multiply by 1). When multiplied by True
>> gives
>> 1; when multiplied by False gives 0.
>>
>> And then SumProduct adds them up.
>>
>> You can't use the FIND("abc",A1:A9) separately - it needs to be inside
>> the
>> SumProduct because of the range in the second parameter. However, you
>> could
>> use FIND("abc",A1) to check for "abc" in A1.
>>
>> HTH
>>
>> Regards, Dave S
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister
> group. This group will not allow for posting of emails, but will allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
