SUMIF calculates number of cells not value of cells

[russkris]

Hi,

I have a couple of tables like the picture below and a summary sheet so I can create some pretty graphs etc.
However the formula to separate the difference between Normal and Overtime counts are backwards and in number of cells not total time.

So the formula for the above is

=SUMIF(Table5[[#Headers],[#Data],[Rate]],"*"&Table36[[#Headers],[Normal]:[Overtime]]&"*",Table5[[#Headers],[#Data],[Hours Worked]])

The value should be 25:00 for Normal and 5:00 for Overtime

Anyone able to point of my mistake?

 

[barry houdini]

Hell russkris,

Your formulas are OK but you need to change the formatting. Format result cells with custom format [h]:mm and you should see 25:00 rather than 01:00

hh:mm format shows "clock time" so you never get a value > 23:59, [h]:mm gives you "elapsed time"

 

[russkris]

OMG!!!!! Dude... Thank you soooo much..

 

[russkris]

Not sure if I need a new thread.

=SUMIF(Table1[[#Headers],[#Data],[Rate]],"*"&Table1[[#Headers],[Normal]:[Overtime]]&"*",Table1[[#Headers],[Convert]])*[@[Hourly Rate]]

Trying to get excel to separate the Normal and Overtime and multiply it by the hourly rate. The above returns a VALUE error.
Normal Hourly rate will be x1 of the Hourly Rate
Overtime will be x2 of the hourly rate

Any tips? Would it be better to add sometime to the hourly rate column to say it it says Overtime, make it x2?
To confuse the issue, on the 1st of December there will be a rate increase.

 

[russkris]

Semi sorted it
Overtime
=SUMIF(C4,"*"&Table1[[#Headers],[Normal]:[Overtime]]&"*",H4)*[@[Hourly Rate]]*2

Normal
=SUMIF(C4,"*"&Table1[[#Headers],[Normal]:[Overtime]]&"*",H4)*[@[Hourly Rate]]