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 J3:J41 that 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 ablove, but
instead of using the latest date, it uses the second latest date.

Thanks for any help.

=SUMPRODUCT(--(E3:E41="UK"),--(J3:J41=MAX(G2:J2)),I3:I41)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
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 J3:J41 that 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 ablove, but
instead of using the latest date, it uses the second latest date.

Thanks for any help.

Hi Bernard,

Thanks for the help but that did not work.

The important point is that I do not know which column will have the latest
date, so it is no good specifying column J , because it could be colums G, H
or I that have the latest date.

I imagine you would need to use the cell range G3:J41 instead.

Any ideas?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(E3:E41="UK"),--(J3:J41=MAX(G2:J2)),I3:I41)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
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 J3:J41 that 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 ablove, but
instead of using the latest date, it uses the second latest date.

Thanks for any help.

I misunderstood your Q. Try this
=SUMPRODUCT((E3:E8="uk")*G3:J8*(G2:J2=MAX(G2:J2)))
If worked on a 5 row test data set; change 8 to 41 everywhere
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
Hi Bernard,

Thanks for the help but that did not work.

The important point is that I do not know which column will have the
latest
date, so it is no good specifying column J , because it could be colums G,
H
or I that have the latest date.

I imagine you would need to use the cell range G3:J41 instead.

Any ideas?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(E3:E41="UK"),--(J3:J41=MAX(G2:J2)),I3:I41)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
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 J3:J41 that 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 ablove, but
instead of using the latest date, it uses the second latest date.

Thanks for any help.

That worked, Thank-you!

"Bernard Liengme" wrote:

I misunderstood your Q. Try this
=SUMPRODUCT((E3:E8="uk")*G3:J8*(G2:J2=MAX(G2:J2)))
If worked on a 5 row test data set; change 8 to 41 everywhere
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
Hi Bernard,

Thanks for the help but that did not work.

The important point is that I do not know which column will have the
latest
date, so it is no good specifying column J , because it could be colums G,
H
or I that have the latest date.

I imagine you would need to use the cell range G3:J41 instead.

Any ideas?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(E3:E41="UK"),--(J3:J41=MAX(G2:J2)),I3:I41)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"carol" wrote in message
...
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 J3:J41 that 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 ablove, but
instead of using the latest date, it uses the second latest date.

Thanks for any help.