Advanced 'SUMPRODUCT' formula - MAX or LARGE??

[carol]

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.

 

[Bernard Liengme]

=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

 

[carol]

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]

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

 

[carol]

That worked, Thank-you!

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