HELP ME PLEASE~ I NEED A FORMULA FIX!

[Adnohral]

I have created calendars in Excel to track my emp. personal time. My dilemma is that I used letters in the calenders so P=1 and the formula counts the days but we allow them to use half days so I need the formula to recognize PH as 0.5 and count that as well. How would I go about doing that and incorporating it into my formula

EX:
Jane takes the 28 and 29 as personal days and she uses a whole and a half day so in essense she has a P and a PH how can I get the formula to count 1.5?

HELP PLEASE

 

[watkins6878]

could you post a copy of the formula please?

 

[Krishnakumar]

Hi Adnohral,

Welcome to MrExcel!

Try,

=SUM(IF(EXACT(B2:B19,"p"),1,IF(EXACT(B2:B19,"ph"),0.5)))

Confirmed with Ctrl+Shift+Enter

change the range to suit.

HTH

 

[Adnohral]

Watkins,
Here is a copy of what I currently have in place that needs to be modified. =COUNTIF(A12:G16,"P")
And this counts up the number of P's in the range and that works but what I need is for it to count the number of PHs in the range as well and assign the PH a value of .5 since it counts P as 1, does that make sense? I hope I am explaining that properly.
Krishnakumar, thanks but that formula did not work, for me. I updated the ranges and it did not calc.

Any more suggestions would be greatly appreciated.
Thanks so much

 

[GaryAtWork]

Hi,

try this adnohral....

=COUNTIF(A12:G16,"P")+(COUNTIF(A12:G16,"PH")*0.5)

 

[Krishnakumar]

Or..

=SUMPRODUCT(COUNTIF(A12:G16,{"P","PH"})*{1,0.5})

HTH

 

[Adnohral]

Thank you guys! That was exactly what I needed, I truely appreciate it.

Rhonda in Atlanta, GA!