Changing COUNTIF to SUMIF using letters before numbers

[KasieJ]

Hello everyone and thanks for reading my first post. I'm not exactly great with the formulas in Excel and would really appreciate any help on this one.

If this question has been answered before and anyone can provide a link to information that would work too. I've been researching this all week and haven't found anything to answer my questions.

I found a file online that someone created which fit my usage 90%. (see attached)

The problem is that our company uses hours for every employee instead of days. So anywhere from 0.5-8 hours can be taken as either Other, Unpaid, or Vacation time.

Is there anyway to change each COUNTIF to a SUMIF (or another function) and include a way to recognize the numbers as "O8" or "V3"? I've tried everything I can think off but it's a little too complicated for me.

I just need to be able to track each employee accordingly through the entire year while having the calendar feature available for record keeping.

Any ideas? Maybe even a different template altogether? lol

Thank you!!

[NBVC]

Do you mean perhaps in P10 of the monthly sheets...

=SUMPRODUCT(--(LEFT($N10:$AR10)=I$8),("0"&MID($N10:$AR10,2,10))+0)

copied across and down?

[Ron Coderre]

Using your posted workbook....These formulas return the appropriate hours

Please Login or Register  to view this content.

Copy that formula across through M10 (adjust column widths as necessary)

Note: If your work day is not 8 hours...change the 8's to the correct value.


Is that something you can work with?

[magicool]

Hello,
Also try this in I11:

Please Login or Register  to view this content.

This will look at p4, p5, p10 etc and sum only the numbers 4+5+10 etc

[KasieJ]

Fantastic! Those formulas are great. I don't have any problem with changing the formula on each sheet, as long as it works.

Thank you so much! I've been trying to get this for a week. Solved :D