I would like to use the following formula to count values less than 10 within a particular range, but the formula gives me a wrong (too high) result because it is including blank cells in its calculation. Can you please tell me how I can modify this formula to ignore any blank cells when it calculates.
=SUMPRODUCT((BS$11:BS$997<10)*(BQ$11:BQ$997>=CA11)*(BR$11:BR$997<=CB11))
Thank you
As you did not specify which column contains blanks, I'll assume it is BR
=SUMPRODUCT((BS$11:BS$997<10)*(BQ$11:BQ$997>=CA11)*(BR$11:BR$997<=CB11)*(BR$11:BR$997<>" "))
FWIW you can also use the SUMIFS function which is probably faster
May be:
=SUMPRODUCT((BS$11:BS$997>0)*(BS$11:BS$997<10)*(BQ$11:BQ$997>=CA11)*(BR$11:BR$997<=CB11))
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks