how can I use an hlookup in a sumif?
 

I have 3 rows of data as follows.

The first row (f1:Ai1), contains dates in the format mm/dd.
The second row (f2:ai2), contains a number representing some amount of time
in minutes, as an example 6 hours would be 360, 7 hours would be 420.
The third row is an interger representing sequential days worked in a given
period of time. 1, 2, 3 ...30.

I have an Hlookup table that looks at the system clock (=today()) and the
three rows mentioned above to determine the sequential of todays date. This
is in cell AR3.

In cell, D5 I would like to use the sumif function to determine how many
minutes total from cell F2 to and including the current day.

Here is what does not work for me sumif(f1:aI1,"<=AR3",f2:ai2). I am open
to any suggestions.

Thanks

how can I use an hlookup in a sumif?
 

If I'm reading this correctly, that cell AR3 holds a sequential, not a date
(mm/dd), then your formula should be:

=sumif(f3:ai3,"<=AR3",f2:ai2)

If that doesn't do it, let us know if you're getting an error or incorrect
result, etc.

Thanks.
--
Mike Lee
McKinney,TX USA


"JMallo" wrote:

I have 3 rows of data as follows.

The first row (f1:Ai1), contains dates in the format mm/dd.
The second row (f2:ai2), contains a number representing some amount of time
in minutes, as an example 6 hours would be 360, 7 hours would be 420.
The third row is an interger representing sequential days worked in a given
period of time. 1, 2, 3 ...30.

I have an Hlookup table that looks at the system clock (=today()) and the
three rows mentioned above to determine the sequential of todays date. This
is in cell AR3.

In cell, D5 I would like to use the sumif function to determine how many
minutes total from cell F2 to and including the current day.

Here is what does not work for me sumif(f1:aI1,"<=AR3",f2:ai2). I am open
to any suggestions.

Thanks

how can I use an hlookup in a sumif?
 

Since AR3 is a cell reference,

=sumif(f3:ai3,"<="&AR3,f2:ai2)


"mikelee101" wrote:

If I'm reading this correctly, that cell AR3 holds a sequential, not a date
(mm/dd), then your formula should be:

=sumif(f3:ai3,"<=AR3",f2:ai2)

If that doesn't do it, let us know if you're getting an error or incorrect
result, etc.

Thanks.
--
Mike Lee
McKinney,TX USA


"JMallo" wrote:

I have 3 rows of data as follows.

The first row (f1:Ai1), contains dates in the format mm/dd.
The second row (f2:ai2), contains a number representing some amount of time
in minutes, as an example 6 hours would be 360, 7 hours would be 420.
The third row is an interger representing sequential days worked in a given
period of time. 1, 2, 3 ...30.

I have an Hlookup table that looks at the system clock (=today()) and the
three rows mentioned above to determine the sequential of todays date. This
is in cell AR3.

In cell, D5 I would like to use the sumif function to determine how many
minutes total from cell F2 to and including the current day.

Here is what does not work for me sumif(f1:aI1,"<=AR3",f2:ai2). I am open
to any suggestions.

Thanks