• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif Problem

K

karthikgoel

New Member
Hi,
a)
I have a master sheet that reads data from different worksheets with the same set of columns. I am trying to count the number of instances in a worksheet where the column (% completion) is 100 or 100%.
= COUNTIF(CAMS!E:E,"100")

This works if in the worksheet data is entered as 100 but if some users may enter data in worksheets as 100% which does not reflect in my count how do i ensure my count works correctly irrespective of whether the data contains 100 or 100%
I even tried giving 100% in place of 100 above but it does not work

b)
Another problem I noticed in worksheets is even if i select say a cell with number 22 as percentage type in format cells its supposed to give 22% but in cells it makes it 2200% how do i ensure it stays as 22%
 
Hi Karthik ,

100 and 100% are not the same ; 100% is actually a numeric value of 1. Any percentage is a value between 0 and 1 ; for example 0.45 is the same as 45%.

The formulae :

=COUNTIF(Range,1)
=COUNTIF(Range,"1")
=COUNTIF(Range,100%)
=COUNTIF(Range,"100%")

are all equivalent , but they are different from :

=COUNTIF(Range,100)

Narayan
 
Thanks Narayan for your inputs

I agree with your comment above, however as different users enter data..they may enter data as 100 or 100% on which I have no control for them both 100 or 100% mean the same and expect it to show up in the aggregate count so what I have done as a workaround is as below

=IF(COUNTIFS(D:D,"100%") > 0,SUM(COUNTIFS(D:D,"100%"),COUNTIFS(D:D,"100")),COUNTIFS(D:D,"100"))

It works fine by counting both 100 and 100%
 
You must log in or register to reply here.
Back
Top