IF forumla - need to populate a cell on one sheet depending on range of cells in another

[Emma2902]

Hi all,

I am hoping youcan help me.

I am trying to setup a spreadsheet to log absence within our business and detail varying levels of sick pay. On one sheet (which I've called Sick Days) I have each employee name in the first column and each column following that represents a day e.g.

column B - 1st Jan
column C - 2nd Jan

The reason it is detailed this way is due to the fact our staff members have different contracted hours and our sickness is logged in hours. So if someone working a 10 hour day is sick, I would put 10 against the relevant date. I have a formula totalling the numbe of sick hours.

This is now where it gets tricky.

Our absence policy means that we pay x amount of hours at full pay and x amount at half pay in any given rolling 12 month period, once this is exhausted the employee goes onto zero pay.
I am therefore adding a row under each employee name where i will detail "Full", "Half" and "Zero" so that we know, at a glance, what was paid for any period of absence.

I have now created a "Totals" sheet where I want to summarise a total for each week in each month. I have created that formula fine, what I am struggling with is the formula for the text. What I want is something like this:

=IF('Sick Days'!AE4:AK4="Full","Half","Zero"'Sick Days'!AE4:AK4)

But when I use that formula, the cell is populated with that foumla, not the result or an error?

Any help would be GREATLY appreciated.

Many thanks in advance.

Emma

[Pete_UK]

Hi Emma,

if the formula is showing in the cell then the cell might be formatted as Text - click on Format Cells and change to General, then edit the cell to bring about the change.

However, the formula doesn't make much sense as it is - do you mean this:

=SUMIF('Sick Days'!AE4:AK4,"Full",'Sick Days'!AE3:AK3)

which will sum from row 3 if row 4 contains "Full".

Hope this helps.

Pete

P.S. My daughter works in Gibraltar

[Emma2902]

Hiya Pete,

Thank you so much for coming back. Apologies for my bum formula, I've not worked with excel on this level for years and it seems I need a refresher course!!

What I want to do is check a range of cells on one sheet which will contain the words "Full", "Half" or "Zero", and then paste whichever word it finds into a cell into a totals or "summary" sheet. Any ideas?

Ooh and really? Small world! Where abouts does she work? I've been here 2 years now.

[Pete_UK]

Well,if those are the only words that can appear in row 4 (and your numbers are in row 3), you can do:

=SUMIF('Sick Days'!AE4:AK4,"<>",'Sick Days'!AE3:AK3)

which means add row 3 numbers when row 4 is not empty. But, if that sheet is just to record sick days, couldn't you just do:

=SUM('Sick Days'!AE3:AK3)

which will add up any numbers in that range.

Emily has been there for 2 and half years - she works for one of the betting companies.

Hope this helps.

Pete

[Emma2902]

Thank you so much for your time Pete, I really appreciate this!

I need to have two formulas running, one totalling the number of hours (as you mention above) but i want the second one to tell me whether or not those hours were paid at full pay, half pay or zero pay. Does that make sense? Hence I want it to check the range from one sheet, then populate the cell with whatever is detailed be that "Full", "Half" or "Zero".

That way I would expect to see the following:

Apr-12
w/c 2nd w/c 9th w/c 16th w/c 23rd
0 0 0 20
Full

Is that clearer? I'm sure it is something so simple but it has just stumped me!

Ah lovely - I also work for one of the gaming companies, there is every possibility I may know her face, especially if she frequents Ocean Village at all - Gibraltar is very small

[Emma2902]

Sorry Ive just seen that my paste didnt come out, basically under W/C 23rd the fist row shows "20" hours and the row under that says "Full" for full pay...

[Pete_UK]

It strikes me that if a person is close to the number of hours paid at Full, then there is a possibility of both "Full" and "Half" occuring in one month, or "Half" and "Zero", so what would you like to do in these cases? You could use this formula to concatenate (join) the text values together for the month:

='Sick Days'!AE4&'Sick Days'!AF4&'Sick Days'!AG4&'Sick Days'!AH4&'Sick Days'!AI4&'Sick Days'!AK4

although if they were off in 2 separate weeks you would get "FullFull", so that is not ideal.

Here's another approach:

=IF(COUNTIF('Sick Days'!AE4:AK4,"Full")>0,"Full","")&IF(COUNTIF('Sick Days'!AE4:AK4,"Half")>0,"Half","")&IF(COUNTIF('Sick Days'!AE4:AK4,"Zero")>0,"Zero","")

which covers most possibilities and doesn't return duplicated words.

Hope this helps.

Pete

Yes, I'm sure you will have seen Emily and Ben around.

[Emma2902]

Wow Pete I think that has pretty much nailed it What a superstar, thank you so much

You are right there is a risk of something going from either full to half or half to full in the same week...but I didnt want to put that in the mix to over complicate the issue, I just thought I would have to try deal with that if the issue arrived...but hopefully your formula has remedied that!

Thanks again and have a fab Christmas.

[Pete_UK]

Okay, well if you think that has answered your question then please mark the thread as Solved - click on Thread Tools above your first post.

Also, you can click on the "star" icon in the bottom left corner of any post that you have found to be helpful, in order to pass on thanks more directly.

Seasons greetings to you, also.

Pete