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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oslopelle - 26 Apr 2008 12:16 GMT

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

Reply to this Message

Dave Peterson - 26 Apr 2008 15:04 GMT

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?

> I am trying to use a lookup-function to determine a different sum
> range for several criteria.
[quoted text clipped - 21 lines]
>
> /Oslopelle

Signature

Dave Peterson

Reply to this Message

Oslopelle - 26 Apr 2008 23:38 GMT

> Try wrapping the =vlookup() with =indirect(vlookup())
>
[quoted text clipped - 34 lines]
>
> - 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
[quoted text clipped - 5 lines]
> > 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.

Reply to this Message

Dave Peterson - 27 Apr 2008 01:44 GMT

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.

> > Try wrapping the =vlookup() with =indirect(vlookup())
> >
[quoted text clipped - 68 lines]
> a is the day, 1 for sunday, 2 for monday and so on, au is a written
> cell reference.

Signature

Dave Peterson

Reply to this Message

Oslopelle - 27 Apr 2008 06:40 GMT

> It worked for me.
>
[quoted text clipped - 84 lines]
>
> - 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

Reply to this Message

Rate this thread: