Hi all,

I use the following formula to look into column E, pick out the cells with
'UK' in it, and then return the corresponding values in column I as a total.

=SUMPRODUCT((E3:E41="UK")*I3:I41)

This formula would work better if I could add a feature that looked into a
range of cells that had only dates in them, and then chose to return the
values that come from the column with the latest date.

e.g. Cells G2:J2 are dates.
Cells E3: E41 has abbreviations, one of which may be 'UK'
Cells G3:J41 have values.

So, I would want to look into G2:J2 to get the latest date, (say J2 had
latest date) then pick out the values from the column with the latest date
i.e. J3:J41 that also have a corresponding 'UK' abbreviation in E3:E41.

I was thinking of using the max or large feature but not sure how to
incorporate it all together.

Also, is it possible to have a formula that does all of the above, but
instead of using the latest date, it uses the second latest date?

Thanks for any help.

Its ok, got it sorted thanks!

"carol" wrote:

Hi all,

I use the following formula to look into column E, pick out the cells with
'UK' in it, and then return the corresponding values in column I as a total.

=SUMPRODUCT((E3:E41="UK")*I3:I41)

This formula would work better if I could add a feature that looked into a
range of cells that had only dates in them, and then chose to return the
values that come from the column with the latest date.

e.g. Cells G2:J2 are dates.
Cells E3: E41 has abbreviations, one of which may be 'UK'
Cells G3:J41 have values.

So, I would want to look into G2:J2 to get the latest date, (say J2 had
latest date) then pick out the values from the column with the latest date
i.e. J3:J41 that also have a corresponding 'UK' abbreviation in E3:E41.

I was thinking of using the max or large feature but not sure how to
incorporate it all together.

Also, is it possible to have a formula that does all of the above, but
instead of using the latest date, it uses the second latest date?

Thanks for any help.