Hi All,
What I want is when cells A2 & B2 are blank, then 1 is counted in the Total Column. This is achieved in the function =COUNTBLANK(A2:B2)-1
I then want this function to be applied to C2 & D2 and also to E2 & F2 as well.
I do not want any count if there is data in the range of the cells nominated i.e. if data is in C2 or D2 or both, then there is to be a zero count.
I only want to count two blank cells as 1.
Thanks
Ray
Last edited by ray963; 05-22-2011 at 03:42 AM.
Hello,
Maybe something like this:
entered as Ctrl + Shift + Enter not Enter only.
Please Login or Register to view this content.
abousetta
It still does not work.
In this formula it enters "1" just with one box of the two being blank.
I need both boxes to be blank before "1" is entered.
Regards
Ray
Hi,
Are you sure you inputted the formula correctly?
Check the attachment.
abousetta
Try this in G2
[EDIT]Please Login or Register to view this content.
@ abousetta
Your formula is not correct, try putting a value in B2, D2, and F2.
Last edited by Marcol; 05-22-2011 at 03:11 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Thanks Marcol. I have modified the formula as follows:
entered as Ctrl + Shift + EnterPlease Login or Register to view this content.
The reason I went a step further was to capture truly blank cells as opposed to cells with text. I have created a sample sheet comparing the sum function and the isblank function and the sum version doesn't recognize text and therefore will still consider the cell to be blank when in fact it is not. Another possible scenario would be to use Len() function:
entered as just EnterPlease Login or Register to view this content.
I have learned that there are many ways to achieve the same intended results, but it sometimes depends on the nature of the cell contents.
Hope this helps.
abousetta
=(--sum(a2+b2=0)+(--sum(c2+d2=0))+(--sum(e2+f2=0)))
Last edited by martindwilson; 05-22-2011 at 10:48 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks