I am using the following formula in a worksheet
some cells in the range B6:B45 are 0 (zero) but I want the formula to ignore these. I have been trying to write a formula based on the above one that only counts cells less than 1 but greater than 0.00001.=SUMPRODUCT(--($A$6:$A$45<>""),--(B$6:B$45<1))
Any ideas?
I'm really struggling. Any help greatfully received.
Hi
You are using 2 different columns in your testing
Try
=SUMPRODUCT(--($A$6:$A$45<>""),--(A$6:A$45<1))
or
=SUMPRODUCT(--($A$6:$A$45>0),--(B$6:B$45<1))
--
Regards
Roger Govier
Microsoft Excel MVP
So you really have three conditions:
=SUMPRODUCT(--($A$6:$A$45<>""), --(B$6:B$45<1), --(B$6:B$45>0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks