can lookup return cell reference istead of "text" for sumif?

[Oslopelle]

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;false)-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]

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]

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?








--

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 AUthe 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]

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]

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.












--

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