Need Help With Lookup & Indirect Function!

[Luke Hall]

Hello,
Thanks for reading this, if you could help me it will be much appreciated
I have a spreadsheet which works something like this below:


Hours Rate ?Total
----------------------------------
6 Standard ?30
5 Standard ?25
3 Overtime ?30
5 Standard ?25
1 Overtime ?10
----------------------------------


The ?Total is determined by the number of hours worked multiplied by one of
2 rates (standard or overtime) the values for standard and overtime are
stored on a different sheet.
What I need from my spreadsheet is what is shown below:


------------------------------------------
| Total Overtime | Total Standard |
-----------------------------------------
| ?40 | ?80 | ;worked out
by adding each ?Total which include particular rate.
-----------------------------------------


I need to work out the separate amounts made in total for each rate
(standard and overtime). I can see that I need to use "indirect" addressing
and "lookup" functions to work out these totals but I'm getting no where.

Please help me

Luke

 

[AndyB]

Luke

I would define names for the cell with the standard and overtime rates in.
You can then use a formula like this:
=IF(H18="Standard",G18*standard,G18*overtime)
where the hours figure is in the G column and the Rate is in the H column

Andy

 

[dvt]

Hours Rate ?Total
----------------------------------
6 Standard ?30
5 Standard ?25
3 Overtime ?30
5 Standard ?25
1 Overtime ?10
----------------------------------

What I need from my spreadsheet is what is shown below: by adding each ?Total which include particular rate.
-----------------------------------------

The SUMIF function will do it. =SUMIF(B3:B7,"Standard",C3:C7) will give you
the total "Standard" hours.

Dave
dvt at psu dot edu

 

[Luke Hall]

The SUMIF function will do it. =SUMIF(B3:B7,"Standard",C3:C7) will give you
the total "Standard" hours.

Dave
dvt at psu dot edu

Cheers for all your replies, i used the SUMIF function and it works
great....

Just one more question......sorry

Using SUMIF can it work out the total of more than one criteria?

i tried changing the formula 100's of times to something like this:
=SUMIF(B3:B7,"Standard" and "Vacation",C3:C7)
but it didn't work

thanks

Luke

PS: the ?(question marks) in my original post where suposed to be 'pound
signs' but somehting went wrong.

 

[Luke Hall]

Cheers for all your replies, i used the SUMIF function and it works
great....

Just one more question......sorry

Using SUMIF can it work out the total of more than one criteria?

i tried changing the formula 100's of times to something like this:
=SUMIF(B3:B7,"Standard" and "Vacation",C3:C7)
but it didn't work

thanks

Luke

PS: the ?(question marks) in my original post where suposed to be 'pound
signs' but somehting went wrong.

It's okay, i fuggered it out:

i used:

=SUM(SUMIF(F4:F18,"Standard",G4:G18)+SUMIF(F4:F18,"Vacation",G4:G18))

is there an easier way than this though?

 

[dvt]

It's okay, i fuggered it out:

i used:

=SUM(SUMIF(F4:F18,"Standard",G4:G18)+SUMIF(F4:F18,"Vacation",G4:G18))

is there an easier way than this though?

Your solution is pretty darn good. I wouldn't waste much time looking for
an easier solution unless you need to do a very large number of categories.
In that case, the OR function might be helpful.

Dave
dvt at psu dot edu