Dears,
My question myght be really very easy but unfortunately I need your help
In my sheet I have a formula that counts the number of "P" in specific range
Formula:
=COUNTIF(PM!$E$3:E4578;"P")
the point is that I would like to adjust the formula in the following way: first it should check if the column is empty-if yes it should return "0" or empty if "no" formula should start counting "P" values.
thanks
Try this array formula...
=IF(AND(PM!$E$3:E4578="");0;COUNTIF(PM!$E$3:E4578;"P"))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Last edited by AlKey; 07-19-2014 at 08:18 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I will test and let you know. Meanwhile can you please explain to me how CTRL+SHIFT+ENTER changes the ameaning of the formula?
I don't understand...
If the column is empty, then certainly the count of "P" within that column would be 0
0 is the value you want if it's empty right?
Last edited by Jonmo1; 07-19-2014 at 09:12 AM.
CTRL+SHIFT+ENTER is used to activate array functionality. Here is another version that don't require to use CTRL+SHIFT+ENTER
=IF(COUNTA(PM!$E$3:E4578)=0;0;COUNTIF(PM!$E$3:E4578;"P"))
That formula looks simpler, but does not work because I should have mentioned that in some of the cell in the column there is a formula which makes the cell value empty but counta detects it as not empty. practically for me it should count it as empty.
=IF(COUNTA(PM!$E$3:E4578)=0,0,COUNTIF(PM!$E$3:E4578;"P"))
As per the AIKey this formula has to work.
How is the result from that formula any different from the result of the original formula?
@ Jonmo1
LOL! It is not
Now if the intended purpose here is to differentiate between
A) 0 result because the column is Empty
or
B) 0 result when the column is NOT empty, but there are no values that meet the criteria
Then you need to return something other than 0 when the column is empty.
Perhaps a text string like "It's Empty" or just the formula blank ""
=IF(COUNTA(PM!$E$3:E4578)=0;"It's Empty";COUNTIF(PM!$E$3:E4578;"P"))
or
=IF(COUNTA(PM!$E$3:E4578)=0;"";COUNTIF(PM!$E$3:E4578;"P"))
Last edited by Jonmo1; 07-19-2014 at 10:39 AM.
One more
=IF(COUNTIF(PM!$E$3:E4578;"?*")=0;"Empty";COUNTIF(PM!$E$3:E4578;"P"))
That formula works as well.Originally Posted by AlKey
could you please let me know how "?*" that works?
You can try this as well...
with on ?*
Formula:=countif(if(PM!$E$3:E4578=0,PM!$E$3:E4578),"P")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Don't forget to click *
Thank you for the feedback!
This COUNTIF(PM!$E$3:E4578;"?*") will count if there are any character or spaces in the range present.
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
Last edited by AlKey; 07-20-2014 at 07:17 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks