Share via

Countif formula (formula result)

Anonymous
2010-10-02T23:15:27+00:00

If you have a countif formula in a cell and the formula result is the number 1, how do you change formula result to = 0.5 or 1.5 or 2, etc.

Examples:  =COUNTIF(H6:$CR6,  "T") now "T" is the formula result that equals**#1**.  How do I get "T" to equal either the number 0.5 or 1.5 or 2, etc within the same cell. Looks like the #1 is the default of my formula but I would like to be able to use 1 or 0.5. Hope this is more helpful in asking my question.  Thanks much 

Vacation Personal Sick Tardy 1/3/2011 1/4/2011 1/5/2011 1/6/2011
3 0 0 1 v v v
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 1 t
0 0 0 0
0 0 0 0
0 0 0 1 t
0 0 0 0
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

14 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-08-03T20:43:07+00:00

    If I understand what you are looking for this would be the kind of result and formula you would need.

    You would have to designate something different for the half days.

    All you have to do to change the counted value is to put the operation you want performed into the formula. Below this would be the /2, if something else counted twice you would just multiply the results of the countif by 2.

    =COUNTIF(F3:I3, "v") + COUNTIF(F3:I3, "vp")/2

    Vacation Personal Sick Tardy 1/3/2011 1/4/2011 1/5/2011 1/6/2011
    3.5 0 0 1 v v v vp
    0 0 0 0
    0 0 0 0
    0 0 0 0
    0 0 0 0
    0 0 0 1 t
    0 0 0 0
    0 0 0 0
    0 0 0 1 t
    0 0 0 0

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-08-03T20:45:51+00:00

    Please see my response above this would also work for your question.

    Thanks!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2010-10-02T23:58:37+00:00

    If you have a countif formula in a cell and the formula result is the number 1, how do you change formula result to = 0.5 or 1.5 or 2, etc.

    Instead of guessing I'll ask the obvious question...

    How do you determine what value to "change" the result to?

    It sounds like what you want to do is add an additional calculation step to your current formula rather than "change" the result of that formula.

    --

    Biff

    Microsoft Excel MVP

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-10-02T23:30:23+00:00

    If you have a countif formula in a cell and the formula result is the number 1, how do you change formula result to = 0.5 or 1.5 or 2, etc.

    If =COUNTIF(....) gives 1, then

    =COUNTIF(....)-0.5 will give 0.5

    =COUNTIF(....)+0.5 will give1.5

    =COUNTIF(....)+1 will give 2

    Hope this helps / Lars-Åke

    Was this answer helpful?

    0 comments
  5. Anonymous
    2010-10-02T23:29:21+00:00

    Multiply? You didn't say what to do if it does not result in 1, so I figure it should stay the same. Assuming you want to change 1 to 0.5...

    =IF(COUNTIF(<range>,<search value>)=1,0.5,1)*COUNTIF(<range>,<search value>)

    Substitute 1.5 or 2 for the 0.5 as needed.

    NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

    Was this answer helpful?

    0 comments