Excel Help Please

[Sandycheeks]

Hi,
Could someone please tell me the formula for the following...

I want to sum a range of cells with the output in 1 cell.
I want to count H = 1 and /H = 0.5.

If the range of cells has | H | H | /H | I want the output cell to read 2.5.

I hope I've explained that clearly enough.

I have used countif to count each H as 1 but I don't know how to tell it to also count /H as 0.5.

TIA.

 

[suenstar]

If I'm reading that correct, you have a table where a column has records with either "H" or "/H" in them and want to count them both at the same time in a single output?

So something like this should work:

=(COUNTIF(B2:B11,"H")+(COUNTIF(B2:B11,"/H")/2))

 

[Sandycheeks]

Thanks, that works great.

I have another issue now though that is probably easy enough to solve but I don't know how...
When I try to sum all of the output cells together it doesn't count the numerical outputs.

I'm making a staff calendar. I want to add absence, holiday (H or /H), and lates per month. The formula you stated works fine for this but I now want a master total for the year, by adding up all of the H output cells.

 

[RoyMi6]

What formula do you have in your "total holidays" cell.

If you had something like:

| Week | Mon | Tue | Wed | Thur | Fri | Holidays
| Week 1 |........|H.....|/H....|........|......| 1.5
| Week 2 |H......|.......|.......|........|......| 1
etc
etc
Total: 2.5

Then a total would just be:
=SUM(G2:G53)

If that doesn't work then you have a real problem...