I am trying to use a lookup-function to determine a different sum
range for several criteria.
Like so:
=Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;f alse)-Sumif($A$7:$A
$1447;"<"&y3;vlookup(e3;AT3:AU11;2;false)
The problem is that the vlookup returns text and not the cell
reference. Is there a way to get the answer from the lookup expressed
as cell reference instead of text, since sumif can't use text, just
the cell reference?

I use it to calculate the number of hours the staff should be paid, so
it's different from weekdays to saturdays, holidays and sundays
in at3 to at11 i have the days (1 for sunday) of the week and in au3
to au11 i have the ranges for the reference table with pay per minute.
1 sheet1!$E$7:$E$1447
2 sheet1!$C$7:$C$1447
3 sheet1!$C$7:$C$1447
4 sheet1!$C$7:$C$1447
5 sheet1!$C$7:$C$1447
6 sheet1!$C$7:$C$1447
7 sheet1!$D$7:$D$1447
holliday sheet1!$F$7:$F$1447
eve sheet1!$G$7:$G$1447

/Oslopelle

Try wrapping the =vlookup() with =indirect(vlookup())

=Sumif($A$7:$A$1447;"<"&X3;indirect(vlookup(e3;AT3 :AU11;2;false)))
-Sumif($A$7:$A$1447;"<"&y3;indirect(vlookup(e3;AT3: AU11;2;false)))

(aren't you missing some ()'s in your posted formula?

Oslopelle wrote:
>
> I am trying to use a lookup-function to determine a different sum
> range for several criteria.
> Like so:
> =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;f alse)-Sumif($A$7:$A
> $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false)
> The problem is that the vlookup returns text and not the cell
> reference. Is there a way to get the answer from the lookup expressed
> as cell reference instead of text, since sumif can't use text, just
> the cell reference?
>
> I use it to calculate the number of hours the staff should be paid, so
> it's different from weekdays to saturdays, holidays and sundays
> in at3 to at11 i have the days (1 for sunday) of the week and in au3
> to au11 i have the ranges for the reference table with pay per minute.
> 1 sheet1!$E$7:$E$1447
> 2 sheet1!$C$7:$C$1447
> 3 sheet1!$C$7:$C$1447
> 4 sheet1!$C$7:$C$1447
> 5 sheet1!$C$7:$C$1447
> 6 sheet1!$C$7:$C$1447
> 7 sheet1!$D$7:$D$1447
> holliday sheet1!$F$7:$F$1447
> eve sheet1!$G$7:$G$1447
>
> /Oslopelle

--

Dave Peterson

On 26 Apr, 16:04, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Try wrapping the =vlookup() with =indirect(vlookup())
>
> =Sumif($A$7:$A$1447;"<"&X3;indirect(vlookup(e3;AT3 :AU11;2;false)))
> -Sumif($A$7:$A$1447;"<"&y3;indirect(vlookup(e3;AT3: AU11;2;false)))
>
> (aren't you missing some ()'s in your posted formula?
>
>
>
>
>
> Oslopelle wrote:
>
> > I am trying to use a lookup-function to determine a different sum
> > range for several criteria.
> > Like so:
> > =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;f alse)-Sumif($A$7:$A
> > $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false)
> > The problem is that the vlookup returns text and not the cell
> > reference. Is there a way to get the answer from the lookup expressed
> > as cell reference instead of text, since sumif can't use text, just
> > the cell reference?
>
> > I use it to calculate the number of hours the staff should be paid, so
> > it's different from weekdays to saturdays, holidays and sundays
> > in at3 to at11 i have the days (1 for sunday) of the week and in au3
> > to au11 i have the ranges for the reference table with pay per minute.
> > 1 * * * sheet1!$E$7:$E$1447
> > 2 * * * sheet1!$C$7:$C$1447
> > 3 * * * sheet1!$C$7:$C$1447
> > 4 * * * sheet1!$C$7:$C$1447
> > 5 * * * sheet1!$C$7:$C$1447
> > 6 * * * sheet1!$C$7:$C$1447
> > 7 * * * sheet1!$D$7:$D$1447
> > holliday * * * *sheet1!$F$7:$F$1447
> > eve * * sheet1!$G$7:$G$1447
>
> > /Oslopelle
>
> --
>
> Dave Peterson- Dölj citerad text -
>
> - Visa citerad text -


unfortunatley i ony get an error message when trying to make an
indirect function. In swedish it comes out as beräkningsbar -
calculable an the sumif won't accept that as a cell reference in the
sum range.
I now have:
Sumif(sheet1!$A$7:$A$1447;"<"&X3;vlookup(e3;sheet1 !AT3:AU11;2;false))-
sumif(sheet1!$A$7:$A$1447;"<"&Y3;vlookup(e3;sheet1 !AT3:AU11;2;false))

if e3= 4
the lookup would return the answer "sheet1!$c$7:$c$1447"


AT AU
> > 1 sheet1!$E$7:$E$1447
> > 2 sheet1!$C$7:$C$1447
> > 3 sheet1!$C$7:$C$1447
> > 4 sheet1!$C$7:$C$1447
> > 5 sheet1!$C$7:$C$1447
> > 6 sheet1!$C$7:$C$1447
> > 7 sheet1!$D$7:$D$1447
> > holliday sheet1!$F$7:$F$1447
> > eve sheet1!$G$7:$G$1447
> >
the problem is that the sumif does not recognize that result as a cell
reference. it thinks it is text and the sumif returns an error.How do
i change this? it only needs to recognize the result as a range.

a contains a day in minutes, x is the beginning time of the empployee,
y is the ending time of the employee, c,d, e, f, g are the respective
compensation the emplyee gets for working that minute (double time=2,
150% =1,5, 133%=1,33 or normal time=1).

a is the day, 1 for sunday, 2 for monday and so on, au is a written
cell reference.

It worked for me.

I don't have another suggestion--except for you to try it again.

You could explain where each range is--is the table on sheet1 or the sheet with
the formula?

And please copy|paste the formula you're using from the formula bar. Don't type
it into the message. Too many things can go wrong.

Oslopelle wrote:
>
> On 26 Apr, 16:04, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Try wrapping the =vlookup() with =indirect(vlookup())
> >
> > =Sumif($A$7:$A$1447;"<"&X3;indirect(vlookup(e3;AT3 :AU11;2;false)))
> > -Sumif($A$7:$A$1447;"<"&y3;indirect(vlookup(e3;AT3: AU11;2;false)))
> >
> > (aren't you missing some ()'s in your posted formula?
> >
> >
> >
> >
> >
> > Oslopelle wrote:
> >
> > > I am trying to use a lookup-function to determine a different sum
> > > range for several criteria.
> > > Like so:
> > > =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;f alse)-Sumif($A$7:$A
> > > $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false)
> > > The problem is that the vlookup returns text and not the cell
> > > reference. Is there a way to get the answer from the lookup expressed
> > > as cell reference instead of text, since sumif can't use text, just
> > > the cell reference?
> >
> > > I use it to calculate the number of hours the staff should be paid, so
> > > it's different from weekdays to saturdays, holidays and sundays
> > > in at3 to at11 i have the days (1 for sunday) of the week and in au3
> > > to au11 i have the ranges for the reference table with pay per minute.
> > > 1 sheet1!$E$7:$E$1447
> > > 2 sheet1!$C$7:$C$1447
> > > 3 sheet1!$C$7:$C$1447
> > > 4 sheet1!$C$7:$C$1447
> > > 5 sheet1!$C$7:$C$1447
> > > 6 sheet1!$C$7:$C$1447
> > > 7 sheet1!$D$7:$D$1447
> > > holliday sheet1!$F$7:$F$1447
> > > eve sheet1!$G$7:$G$1447
> >
> > > /Oslopelle
> >
> > --
> >
> > Dave Peterson- Dölj citerad text -
> >
> > - Visa citerad text -
>
> unfortunatley i ony get an error message when trying to make an
> indirect function. In swedish it comes out as beräkningsbar -
> calculable an the sumif won't accept that as a cell reference in the
> sum range.
> I now have:
> Sumif(sheet1!$A$7:$A$1447;"<"&X3;vlookup(e3;sheet1 !AT3:AU11;2;false))-
> sumif(sheet1!$A$7:$A$1447;"<"&Y3;vlookup(e3;sheet1 !AT3:AU11;2;false))
>
> if e3= 4
> the lookup would return the answer "sheet1!$c$7:$c$1447"
>
> AT AU
> > > 1 sheet1!$E$7:$E$1447
> > > 2 sheet1!$C$7:$C$1447
> > > 3 sheet1!$C$7:$C$1447
> > > 4 sheet1!$C$7:$C$1447
> > > 5 sheet1!$C$7:$C$1447
> > > 6 sheet1!$C$7:$C$1447
> > > 7 sheet1!$D$7:$D$1447
> > > holliday sheet1!$F$7:$F$1447
> > > eve sheet1!$G$7:$G$1447
> > >
> the problem is that the sumif does not recognize that result as a cell
> reference. it thinks it is text and the sumif returns an error.How do
> i change this? it only needs to recognize the result as a range.
>
> a contains a day in minutes, x is the beginning time of the empployee,
> y is the ending time of the employee, c,d, e, f, g are the respective
> compensation the emplyee gets for working that minute (double time=2,
> 150% =1,5, 133%=1,33 or normal time=1).
>
> a is the day, 1 for sunday, 2 for monday and so on, au is a written
> cell reference.

--

Dave Peterson

On 27 Apr, 02:44, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> It worked for me.
>
> I don't have another suggestion--except for you to try it again.
>
> You could explain where each range is--is the table on sheet1 or the sheetwith
> the formula?
>
> And please copy|paste the formula you're using from the formula bar. *Don't type
> it into the message. *Too many things can go wrong.
>
>
>
>
>
> Oslopelle wrote:
>
> > On 26 Apr, 16:04, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Try wrapping the =vlookup() with =indirect(vlookup())
>
> > > =Sumif($A$7:$A$1447;"<"&X3;indirect(vlookup(e3;AT3 :AU11;2;false)))
> > > -Sumif($A$7:$A$1447;"<"&y3;indirect(vlookup(e3;AT3: AU11;2;false)))
>
> > > (aren't you missing some ()'s in your posted formula?
>
> > > Oslopelle wrote:
>
> > > > I am trying to use a lookup-function to determine a different sum
> > > > range for several criteria.
> > > > Like so:
> > > > =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;f alse)-Sumif($A$7:$A
> > > > $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false)
> > > > The problem is that the vlookup returns text and not the cell
> > > > reference. Is there a way to get the answer from the lookup expressed
> > > > as cell reference instead of text, since sumif can't use text, just
> > > > the cell reference?
>
> > > > I use it to calculate the number of hours the staff should be paid, so
> > > > it's different from weekdays to saturdays, holidays and sundays
> > > > in at3 to at11 i have the days (1 for sunday) of the week and in au3
> > > > to au11 i have the ranges for the reference table with pay per minute.
> > > > 1 * * * sheet1!$E$7:$E$1447
> > > > 2 * * * sheet1!$C$7:$C$1447
> > > > 3 * * * sheet1!$C$7:$C$1447
> > > > 4 * * * sheet1!$C$7:$C$1447
> > > > 5 * * * sheet1!$C$7:$C$1447
> > > > 6 * * * sheet1!$C$7:$C$1447
> > > > 7 * * * sheet1!$D$7:$D$1447
> > > > holliday * * * *sheet1!$F$7:$F$1447
> > > > eve * * sheet1!$G$7:$G$1447
>
> > > > /Oslopelle
>
> > > --
>
> > > Dave Peterson- Dölj citerad text -
>
> > > - Visa citerad text -
>
> > unfortunatley i ony get an error message when trying to make an
> > indirect function. In swedish it comes out as beräkningsbar -
> > calculable an the sumif won't accept that as a cell reference in the
> > sum range.
> > I now have:
> > Sumif(sheet1!$A$7:$A$1447;"<"&X3;vlookup(e3;sheet1 !AT3:AU11;2;false))-
> > sumif(sheet1!$A$7:$A$1447;"<"&Y3;vlookup(e3;sheet1 !AT3:AU11;2;false))
>
> > if e3= 4
> > the lookup would return the answer "sheet1!$c$7:$c$1447"
>
> > AT * * * * * *AU
> > > > 1 * * * * sheet1!$E$7:$E$1447
> > > > 2 * * * * sheet1!$C$7:$C$1447
> > > > 3 * * * * sheet1!$C$7:$C$1447
> > > > 4 * * * * sheet1!$C$7:$C$1447
> > > > 5 * * * * sheet1!$C$7:$C$1447
> > > > 6 * * * * sheet1!$C$7:$C$1447
> > > > 7 * * * * sheet1!$D$7:$D$1447
> > > > holliday *sheet1!$F$7:$F$1447
> > > > eve * * * sheet1!$G$7:$G$1447
>
> > the problem is that the sumif does not recognize that result as a cell
> > reference. it thinks it is text and the sumif returns an error.How do
> > i change this? it only needs to recognize the result as a range.
>
> > a contains a day in minutes, x is the beginning time of the empployee,
> > y is the ending time of the employee, c,d, e, f, g are the respective
> > compensation the emplyee gets for working that minute (double time=2,
> > 150% =1,5, 133%=1,33 or normal time=1).
>
> > a is the day, 1 for sunday, 2 for monday and so on, au is a written
> > cell reference.
>
> --
>
> Dave Peterson- Dölj citerad text -
>
> - Visa citerad text -

It does work, it was just me and bad syntax!!
Thanks a million for the help! It sure made my day!!
/Oslopelle