Advanced 'SUMPRODUCT' formula - MAX or LARGE??

C

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.
 
C

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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT - enhanced formula 1
Sumproduct and Count If 1
MAX value but with two comparison filtering criteria 5
Sumproduct formula 3
Help With A Golf League 4
Help with formula 2
Formula Problem 2
counting dates using SUMPRODUCT 4

Top