Multiple IF and ISTEXT within same formula.

[myactiondesign]

Hello,

I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.

At the bottom I have another SUM formula to calculate total daily hours.

However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.

Please see below:

Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.

However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.

"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"

What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.

The only way I could think of doing it was something along these lines:

"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"

The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.

So, to the question:

Is there a way around this issue?

Any help, as always, is much appreciated, and thank you in advance!

!&...

 

[RonB1111]

If you are asking for a formula for the "Daily Hours" Total shown at the bottom of you example table which excludes rows that have text in column F, try:

=SUMIFS(H2:H31,F2:F31,">="&0)

You may need to adjust the ranges H2:H31 and F2:F31 to reflect your actual data.

 

[myactiondesign]

If you are asking for a formula for the "Daily Hours" Total shown at the bottom of you example table which excludes rows that have text in column F, try:

=SUMIFS(H2:H31,F2:F31,">="&0)

You may need to adjust the ranges H2:H31 and F2:F31 to reflect your actual data.

Hi RonB1111,

Thanks for your help.

The formula is almost exactly what I need, however the results I am getting are now only for holiday pay.

=SUMIFS(H16:H31, F16:F31, ">=&0")

Is there someway to make this not equal a specific value range, or more specifically for my needs, to equal text?

=SUMIFS(H16:H31, F16:F31, ">=&0")
[sumifs(these values, if these values, do not equate to text)] - thus ignoring only the holiday pay.

Getting really close. If you have any more thoughts, it would be much appreciated.

!&...

 

[myactiondesign]

Figured it out!

It's a little long-winded, but it works.

=(-1)*(SUMIFS(H16:H31, F16:F31, ">=&0")-SUM(H16:H31))

The original SUMIFS only gave me holiday hours, so I subtracted a sum of the total hours from that, and multiplied by negative one to produce a positive trading hour only result.

Thanks again Ron for the headstart!

Kind Regards,
!&...

 

[RonB1111]

Glad you've got a working formula, but you put the " in the wrong place -should be after the = and not after the 0. Try

=SUMIFS(H16:H31, F16:F31, ">="&0)

 

[myactiondesign]

Glad you've got a working formula, but you put the " in the wrong place -should be after the = and not after the 0. Try

=SUMIFS(H16:H31, F16:F31, ">="&0)

*Mind Blown*

Excel is amazing, and a right pain at times!

That tiny little mistake made me take a long-winded way round to reach the same answer that your original formula actually got!

You live and learn.

!&...