How to combine LARGE and SUMPRODUCT

[Phillycheese5]

Hi,
I have a SUMPRODUCT formula that works great to sum multiple criteria. Specifically, from 3 columns it finds a particular date and
sums values if they have transaction codes "VISA", "MCARD", or "DISC". However, I need to use the LARGE function on the same subset but do not know how to do this. I have attached a sample file and highlighted the cells in row 75 where I would like to use a
formula. The existing formula is:
=IF(F75="","",SUMPRODUCT(--(C40:C75=F75),D40:D75,--ISNUMBER(MATCH(E40:E75,{"MCARD","VISA","DISC"},0))))
The IF is used based on a helper column with dates in order to only display the last row's data when there are multiple transactions
on the same day.
Any suggestions would be appreciated.

Thanks,
Phillycheese

[ChemistB]

Try this arrayed formula

=LARGE(IF($C$40:$C$75=$F75, IF(($E$40:$E$75="Visa")+($E$40:$E$75="MCard")+($E$40:$E$75="Disc"), $D$40:$D$75)), COLUMNS($H$75:H$75))

[José Augusto]

Hi
Try these and copy right

Please Login or Register  to view this content.

[Phillycheese5]

Hi ChemistB---this formula works great, thank you!!

Hi Jose---the AGGREGATE formula works great as well, although it shows a zero instead of the -41.85 which would be the sixth largest. Is there a way to show the actual value?

[José Augusto]

Is there a way to show the actual value?

Hi Philly
Yes. Use these formula

Please Login or Register  to view this content.

Regards.

[Phillycheese5]

Jose,
That's pretty crazy stuff!! It works great, thank you!!! If you have a moment, would you mind walking me through the syntax so I can better understand what's going on here...in case I ever need to tweak it, at least I can make a reasonable attempt :-)
Thanks again,
Phillycheese

[Aladin Akyurek]

You can use the same logic for multiple-value OR...

In H75 control+shift+enter, not just enter, and copy across:

Please Login or Register  to view this content.

[Phillycheese5]

Thanks for the additional formula Aladin!