Sum product problem!! Help!!

  • Thread starter Thread starter office
  • Start date Start date
O

office

I have 10 people working for me, they all average 40ish hours a week.

There normal working week is Mon to Fri 7am until 5pm this is all rate 0
If they work overtime during the week (before 7am & after 5pm) its called
rate 1
If they work overtime at the weekend its called rate 2

I log there timesheet hours on a weekly basis using a spread sheet.
I have a rate column in which enter 0, 1, or 2 depending on when they worked
and then the hours at that rate.

Each of the people working for me have an unique personnel number this is in
cell A4 - A14 on my hours sheet.

=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=0)*(Sheet1!V$3:V$123))
Rate 0 column cells
=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=1)*(Sheet1!V$3:V$123))
Rate 1 column cells
=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=2)*(Sheet1!V$3:V$123))
Rate 2 column cells

These are the formulae I'm using at the moment on my hours sheet.
The problem is that this formula only looks from row 3 to 123, I want it to
look at the whole sheet and ignore any empty rows.
Most of the time $123 is not correct and I always have to change it.
sometimes there will only be $27 entries from all the timesheets and other
times there can be as many as $209.
Is there anyway I can get excel to automatically find this number its self
or just look at the rows that have something in them.
I've tried using ranges with no luck.

Thanx for any help.

Rick
 
Rick,

Set up your formula for more rows than you will ever need: 1000 rows
seems like enough. The extra rows will simply be ignored, as long as
they are blank.

If, instead, you are using the rows below as storage for old data,
then I would suggest moving the data to another sheet instead, or
using an additional criteria in your sumproduct formula to ignore the
older data.

HTH,
Bernie
MS Excel MVP
 
office said:
I have 10 people working for me, they all average 40ish hours a week.

There normal working week is Mon to Fri 7am until 5pm this is all rate 0
If they work overtime during the week (before 7am & after 5pm) its called
rate 1
If they work overtime at the weekend its called rate 2

I log there timesheet hours on a weekly basis using a spread sheet.
I have a rate column in which enter 0, 1, or 2 depending on when they worked
and then the hours at that rate.

Each of the people working for me have an unique personnel number this is in
cell A4 - A14 on my hours sheet.

=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=0)*(Sheet1!V$3:V$123))
Rate 0 column cells
=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=1)*(Sheet1!V$3:V$123))
Rate 1 column cells
=SUMPRODUCT((Sheet1!B$3:B$123=A4)*(Sheet1!F$3:F$123=2)*(Sheet1!V$3:V$123))
Rate 2 column cells

These are the formulae I'm using at the moment on my hours sheet.
The problem is that this formula only looks from row 3 to 123, I want it to
look at the whole sheet and ignore any empty rows.
Most of the time $123 is not correct and I always have to change it.
sometimes there will only be $27 entries from all the timesheets and other
times there can be as many as $209.
Is there anyway I can get excel to automatically find this number its self
or just look at the rows that have something in them.
I've tried using ranges with no luck.

Thanx for any help.

Rick
Click to expand...

Why not just use a range greater than you are ever likely to need, such as
$B$3:$B$1000 ?
Blank rows will not affect this, as the conditions will eveluate as FALSE
for these rows.
 
I would set up 3 defined named ranges on sheet 1by going to
insert>name>define>top box type in ColA>in refers to box type in
=offset($B$3,0,0,counta($B:$B),1) and touch enter
repeat for ColF and ColV except still use $B:$B for the counta
then
=SUMPRODUCT((ColB=A4)*(colF=1)*(colV))
will be self adjusting based on the length of cells in col B

--
Don Guillett
SalesAid Software
(e-mail address removed)
office said:
I have 10 people working for me, they all average 40ish hours a week.

There normal working week is Mon to Fri 7am until 5pm this is all rate 0
If they work overtime during the week (before 7am & after 5pm) its called
rate 1
If they work overtime at the weekend its called rate 2

I log there timesheet hours on a weekly basis using a spread sheet.
I have a rate column in which enter 0, 1, or 2 depending on when they worked
and then the hours at that rate.

Each of the people working for me have an unique personnel number this is in
cell A4 - A14 on my hours sheet.
Click to expand...
Optimum Calculation using VBA
 
I tried this but it returns an error in the cell I want the value in!!

#VALUE! is what it returns.

All the rows/cells that follow are empty/blank.
 
office
The problem is probably due to the last argument in your SUMPRODUCT. This arguement directly reference an area on your sheet. If any of the cells in this region contain text instead of a number your will get the #VALUE error

Good Luck
Mark Graesse
(e-mail address removed)

----- office wrote: ----

I tried this but it returns an error in the cell I want the value in!

#VALUE! is what it returns

All the rows/cells that follow are empty/blank
 
Try this array entered (ctrl-alt-enter) formula to ignore text arguments

=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0)*IF(ISTEXT(Sheet1!V$3:V$1000),0, Sheet1!V$3:V$1000)

Good Luck
Mark Graesse
(e-mail address removed)

----- office wrote: ----

I tried this but it returns an error in the cell I want the value in!

#VALUE! is what it returns

All the rows/cells that follow are empty/blank
 
The formula below doesn't work, and I don't understand what ctrl+alt+enter
is supposed to do as well. Thanx for trying tho!
None of the columns specified in the formula contain any text at all.
All the formatting is set to number not text or general.

Cheers

Rick


Mark Graesser said:
Try this array entered (ctrl-alt-enter) formula to ignore text arguments.
Click to expand...
=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0)*IF(ISTEXT(Sheet1!V$
3:V$1000),0, Sheet1!V$3:V$1000))
 
I think Ctrl+Shift+Enter is what was meant. In other words, type the formula
in. Then, instead of just pressing Enter, hold down Ctrl and Shift whilst
pressing Enter. This is what you need to do when entering an array formula.
If you do this correctly, Excel will add curly brackets { } around the
formula when you look at it in the formula bar. You cannot just type in
these brackets. An array formula will not work unless you array-enter it, as
described.
 
Try this array entered (ctrl-alt-enter) formula to ignore text arguments.
Click to expand...

1. It's [Ctrl]+[Shift]+[Enter], not ..alt.. .
2. Why bother using SUMPRODUCT rather than SUM if you need to enter the formula
as an array formula?
3. You could get screwed up results if cells in Sheet1!V$3:V$1000 evaluate to
True, False or error values, none of which are TEXT, but none of which are
numbers either.
=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0)
*IF(ISTEXT(Sheet1!V$3:V$1000),0, Sheet1!V$3:V$1000))
Click to expand...

If the concern is simply avoiding #VALUE! errors from text in column V, Change
the formula to

=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0),
Sheet1!V$3:V$1000)

This approach was recently mentioned by Dana DeLouis.
 
Try this array entered (ctrl-alt-enter) formula to ignore text arguments.
Click to expand...

1. It's [Ctrl]+[Shift]+[Enter], not ..alt.. .
2. Why bother using SUMPRODUCT rather than SUM if you need to enter the formula
as an array formula?
3. You could get screwed up results if cells in Sheet1!V$3:V$1000 evaluate to
True, False or error values, none of which are TEXT, but none of which are
numbers either.
=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0)
*IF(ISTEXT(Sheet1!V$3:V$1000),0, Sheet1!V$3:V$1000))
Click to expand...

If the concern is simply avoiding #VALUE! errors from text in column V, Change
the formula to

=SUMPRODUCT((Sheet1!B$3:B$1000=A4)*(Sheet1!F$3:F$1000=0),
Sheet1!V$3:V$1000)

This approach was recently mentioned by Dana DeLouis. This formula does *NOT*
need to be array-entered.
 
Back
Top