Adjusting Count of Absences

[mihaufo]

I have attendance which says P for present, A for absent and N for non working day. So, I contact number of absent, present or non working day using CountIF.


Now, my question is to if a person is Absent before and the non working day like ANA, then the total number of Absent becomes 3 instead of 2.


How to do it? Using a macro or any function.

[Glenn Kennedy]

I think we will need to see a sample Excel sheet to understand what values you have in what cells to be able to provide an answer... So please post a sheet in this thread.

[mihaufo]

Here is the sample data

AM_Sample.xlsm

[Glenn Kennedy]

Apologies, i was away from a PC for a while. Here you are. It's what you wanted, i hope...

[mihaufo]

Thanks GK, I got the result I was looking for.

I have another issue, I want the text entered in the field D4:AG31 to be in Upper Case, so I have written a code for that in the Sheet Jan, but for example an employee is present from Mon to Thurs, then I can drag the data entered under Monday to Thursday. but my code five an error Type mismatch.

What should be done?

AM_Sample_V1.xlsm

[Glenn Kennedy]

Hi. I'm totally ignorant when it comes to code.... In fact, the formula works fine with upper and lower case letters... So you don't really need the code at all.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Place your new question in a new thread. If its a vbs question, post it in the programming subforum