SUMPRODUCT or do I need something else???
 

Hello,

I am driving myself mad with this one.

I have a spreadsheet that I record timesheet information on

Col 1 - Operative's name
Col 2 - Job number (there can be any amount of job numbers done in one day)
Col 3 - Totals (placed this end for ease when looking up total hours worked
on each job)
Col 4 - 1st day of the month
Col 5 - 2nd day
col 6 - 3rd day
etc
etc
etc

Below each date there is a dropdown list of the vehicle they could be using
that day. This shows me how many hours they did on each job that day.

The problem I have is when trying to summarise. I can't find a way to pick
out the data I need because some of it is in rows and some in Columns.

I have tried using helper columns but to no advantage. I did mangage to find
one way to get it to work, but its going to take me poaaibly the rest of my
life to write out all the formula's for it, and leave me with such a HUGE
amount of data as I have to ask every possible variant of the question to
ensure I don't get a #REF! problem.

Please please help.

Thanks

SUMPRODUCT or do I need something else???
 

=SUMPRODUCT((D3:AG21="vehicle")*(C2:AG20))


note the offest rows

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"forevertrying" wrote in message
...
Hello,

I am driving myself mad with this one.

I have a spreadsheet that I record timesheet information on

Col 1 - Operative's name
Col 2 - Job number (there can be any amount of job numbers done in one
day)
Col 3 - Totals (placed this end for ease when looking up total hours
worked
on each job)
Col 4 - 1st day of the month
Col 5 - 2nd day
col 6 - 3rd day
etc
etc
etc

Below each date there is a dropdown list of the vehicle they could be
using
that day. This shows me how many hours they did on each job that day.

The problem I have is when trying to summarise. I can't find a way to pick
out the data I need because some of it is in rows and some in Columns.

I have tried using helper columns but to no advantage. I did mangage to
find
one way to get it to work, but its going to take me poaaibly the rest of
my
life to write out all the formula's for it, and leave me with such a HUGE
amount of data as I have to ask every possible variant of the question to
ensure I don't get a #REF! problem.

Please please help.

Thanks

SUMPRODUCT or do I need something else???
 

Hi Bob,

I have re-arranged everything since that last question.

Is there away to do a cross reference lookup. I know I can look in rows or
in columns... but what about a cross of the two?

Thanks

"Bob Phillips" wrote:

=SUMPRODUCT((D3:AG21="vehicle")*(C2:AG20))


note the offest rows

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"forevertrying" wrote in message
...
Hello,

I am driving myself mad with this one.

I have a spreadsheet that I record timesheet information on

Col 1 - Operative's name
Col 2 - Job number (there can be any amount of job numbers done in one
day)
Col 3 - Totals (placed this end for ease when looking up total hours
worked
on each job)
Col 4 - 1st day of the month
Col 5 - 2nd day
col 6 - 3rd day
etc
etc
etc

Below each date there is a dropdown list of the vehicle they could be
using
that day. This shows me how many hours they did on each job that day.

The problem I have is when trying to summarise. I can't find a way to pick
out the data I need because some of it is in rows and some in Columns.

I have tried using helper columns but to no advantage. I did mangage to
find
one way to get it to work, but its going to take me poaaibly the rest of
my
life to write out all the formula's for it, and leave me with such a HUGE
amount of data as I have to ask every possible variant of the question to
ensure I don't get a #REF! problem.

Please please help.

Thanks