SUMIFS or SUMIF Not working. This must be so easy but I can't see what is wrong!

[dellehurley]

I am using Mac Excel 2008


[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]7:36[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]0:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]7:36[/TD]
[TD]p[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]7:36[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

I am using this formula. I want to add the times which have a P in the corresponding column.

=SUMIFS(H15:H28,I15:I28,"p")

but if come back as 0 even thought the times should add up to something else.
Ideally I would like to leave the cell blank if it is 0 rather than 0:00 however I am trying everything I can think of which might fix this.
I have tried SUMIF too. And I have used a cell reference instead of "p" too. Nothing has worked.

I am sure I'm missing something really obvious but I just can't see it!

Thanks for your help.

 

[bschwartz]

It works for me...

For the record, how do you propose to sum time of day with other times of day?

For example, in the above data, what is the desired result?

 

[dellehurley]

Do you think it might be something to do with Mac excel? If is not as good as the ms version!

I am setting up a timesheet. When a staff member takes personal leave they will put a "p" in column I, a "l" for time in lieu and "a" for annual leave.
I want the sum to work out the break up of different types of hours and ultimately work out the time in lieu to be carried forward after each pay period.

Dannielle

 

[dellehurley]

I have just realised that the issue is not with the SUMIFS as I thought. Column H is populated with this formula =IF(OR(I15="P",I15="A"),"7:36","0:00 ")
and it is not seeing the 7:36 for some reason. If I manually type in the 7:36 the SUMIF formula works. Any ideas???????

 

[DavidCoop]

Hi Dannielle,

Your problem is that the IF formula is supplying TEXT instead of time.

Try this: =IF(OR(I15="P",I15="A"),TIME(7,36,0),TIME(0,0,0))

Note: You will have to format the cells in column H as time.
Also note: You will have to Custom Format the cell where you sum the hours as [h]:mm.​ If you don't you will not get the answer you expect.

 

[dellehurley]

That is it!!!! Thanks for your help. Your brilliant.

Any ideas on this one this is for another section on the timesheet
=IF(AND(K35=TRUE,(G29+G34)>=76,(76:00-(G29+G35),0)

Now this does not work. Is I substitute True and False at the end it works. So maybe I cannot have another sum in here but if that is the case I don't know what equation to use.
Thanks
Dannielle

 

[DavidCoop]

Hi Dannielle,

You have a couple of problems with this formula.

I have made some changes:

=IF(AND(K35=TRUE,(G29+G34)>=76),76-(G29+G35),0)

You had missed the close bracket on the AND function, and included text (76:00) in the arithmetic in the true part of the IF statement.

You have to be very careful when performing arithmetic with time. Excel stores time as a fraction of a day (so 8:00am will be 0.3333333 which is a third of a day. Your formula is trying to use whole numbers (76) in conjunction with time which won't work.

If you have a cell that contains time - multiply it by 24 - you will then have time as a decimal. (eg: 8:30am = 0.354167 * 24 = 8.5).

Hope this helps.

Regards,

David

 

[dellehurley]

Thanks David.
I still have an issue and it is obviously because of the time issue.
Both G29 and G34 are times. With the equation above the answer is coming up as 0:00.
So I have added Time to the equation. I tried this but it does not work either.
=IF(AND(K35=TRUE,(G29+G34)>=TIME(76,0,0)),TIME(76,0,0)-(G29+G34),TIME(0,0,0))
Cell format [h]:mm
The answer is coming up as -45:00 it should be -27:00
Thanks for your time and help with this.
Dannielle

 

[DavidCoop]

Good Morning,

I assume you are working with a 76 hour fortnight (you must be in Australia)!

The TIME() function doesn't handle >24 hours. If you put 76 in it, you will get 4 as the answer (24*3 = 72 + 4 = 76). Also, when you're working with time, you can't have a negative answer.

So, try this: =IF(AND(K35=TRUE,(G29+G34)*24>=76),(G29+G34)*24-76,0) You will get 27.

If you want it to come out with 27:00 as time, divide by 24 at the end, and from the cell with [h]:mm

(The square brackets around the hours in the format tells excel to accumulate the hours. Without it, you will get 3:00)

Do you understand what the formula is doing?

Regards,

David

 

[dellehurley]

Thanks David
I think I have it. It all seems to be working now. Thanks for all your help and patience.
Dannielle