SUMPRODUCT - enhanced formula
 

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.

SUMPRODUCT - enhanced formula
 

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.