Max & sumproduct

[Steeviee]

Hello gang,

I'm hoping that you will be patient and help me out here. I am sure that this is possible but have not been able to understand the logic from the archive posts.

I have a table of data in tab 'PO'. I want to extract the latest date from that table. the number of lines varies and so I have used 10,000 as the number of rows.

=SUMPRODUCT(MAX((PO!BC13:$BC$10000=$B5)*(PO!BB13:$BB$10000=$C3),(PO!AD13:$AD$10000)))

Where column BC contains the part number in 'PO' tab and column B in the current tab has the part numbers listed.
Col BB in 'PO' has the category, Col C in the current tab
Col AD in 'PO' has the date

I am trying to find the latest/max date for a particular part number for a particular category.

I just don't know how to do it! Do I need ISNUMBER or SEARCH anywhere, and if so why? Is the data range being larger than the amount of data an issue?

As always, all help much appreciated.

Thanks,

 

[Jonmo1]

Try this array entered with CTRL + SHIFT + ENTER

=MAX(IF(PO!BC13:$BC$10000=$B5,IF(PO!BB13:$BB$10000=$C3,PO!AD13:$AD$10000)))

Hope that helps.

 

[Steeviee]

Perfect. Thank you so much.

I'm just hoping that array formulae can be dragged...

Clever.

 

[Jonmo1]

Perfect. Thank you so much.

I'm just hoping that array formulae can be dragged...

Clever.

Glad to help, thanks for the feedback.


Array formulas can be dragged.