SUMPRODUCT with Max Function Help

[conorfinnegan]

I don't know if I have the right functions but I have an issue that I
am running into with a lot of data I am dealing with. I need to find
the best way to figure this out since it is something I deal with on a
monthly basis. See the sample data below and let me know what excel
functions might help me out if you can. Thanks in advance for your
help.

Run Date Item Sell Start
Sell End Copies Sold

10/1 CD123 10/1/2006
10/31/2006 1000
10/1 CD456 10/1/2006
10/31/2006 3500

10/2 CD123 10/1/2006
10/21/2006 1500
10/2 CD456 10/1/2006
10/31/2006 4500


Basically the scenario/example above shows a line item, in this case
CD123 and CD456...CD123 was scheduled to be sold from the 1st to the
31st and by the date (let's say the 15th), 1000 copies were sold...Then
on the 21st, CD123 was pulled from the shelves and only sold 1500
copies total. I have roughly 6000 lines of data like this that I keep
track of on a daily basis. At the end of the month, I need an easy way
to go back and pull all the line items and have it show me the number
of copies sold. The problem I have is that for 15 days, I have line
items that have a start date of 10/1 and the end date of 10/31, then on
the 15th, I get a new line item that has a new end data of 10/21...Even
though Column A is the same, I can't get the formula to return the max
number if the end date changes. I normally get the 1000 to show up but
not the 1500.

It wouldn't be so bad if I didn't have roughly 7000 rows of data to
sift through. I don't always know when end dates have changed. Please
help if you can. Thanks again.

Conor

 

[Stefi]

I'm not sure I exactly understood your table but this array formula picks up
1500 for CD123:

=MAX(IF(B2:B5="CD123",E2:E5))
Confirm it with Ctrl+Shift+Enter!

Regards,
Stefi


„[email protected]†ezt írta:

 

[Max]

Perhaps ..

Assuming source data in cols A to E,
headers in row1, data from row2 down to row7000 (say)
where col B = Item, col C = Sell Start, col D = Sell End
(real dates assumed in cols C and D)

With the unique items listed in say G2 down, viz:
in G2: CD123
in G3: CD456
etc

Try in H2:
=SUMPRODUCT(($B$2:$B$7000=G2)*($C$2:$C$7000>=--"1 Oct
2006")*($D$2:$D$7000<=--"31 Oct 2006"),$E$2:$E$7000)
Copy H2 down

 

[Max]

To extract the list of unique items in col B into G2 down, you could use
Data > Filter > Advanced Filter (Unique records only).

Select col B, click Data > Filter > Advanced Filter
(Answer OK to Excel's prompt on col labels)

In the Advanced Filter dialog:
check "Copy to another location"
Copy to: G1
check "Unique records only"
Click OK

The col header "Item" will appear in G1,
with the unique list of items in G2 down

 

[Bob Phillips]

This will get you the latest value

=INDEX($E$1:$E$7000,MAX(IF(B2=$B$1:$B$7000,ROW($B$1:$B$7000))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[conorfinnegan]

Thanks to everyone for their help. This worked great.

Conor