Highest sum in a column based on multiple criteria

**alisachsf** · 11-05-2019, 07:00 PM

Hi all,
I'm trying to find in a table the highest sum of rows based on specific criteria.
For example:

Deal Name Date Amount
Deal 1 Name 1 Oct-19 $150.00
Deal 2 Name 1 Nov-19 $250.00
Deal 1 Name 2 Nov-19 $100.00
Deal 3 Name 2 Aug-19 $300.00
Deal 2 Name 3 Aug-19 $500.00
Deal 1 Name 4 Oct-19 $150.00
Deal 2 Name 5 Oct-19 $700.00
Deal 3 Name 5 Sep-19 $300.00

a. I want to find what would be the deal number with the highest sum of amount if the deal was in October and is Name 1.
b. What is the sum of that largest deal

I'm guessing I need an array formula, and tried playing with =Large(if) but nothing seem to get me where I want.

Thanks!

**Pete_UK** · 11-05-2019, 08:44 PM

You can use this array* formula in D18 to get the largest sum for those criteria:

=MAX(IF(($B$2:$B$9=C14)*($C$2:$C$9=C15),$D$2:$D$9))

and use this array* formula in C13 to get the deal number:

=INDEX($A$2:$A$9,MATCH(1,($D$2:$D$9=D18)*($B$2:$B$9=C14)*($C$2:$C$9=C15),0))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

Hope this helps.

Pete

Highest sum in a column based on multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Highest sum in a column based on multiple criteria

Re: Highest sum in a column based on multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find highest text value in column based on criteria

Formula to Sum 3 Column Values based on Multiple Criteria for Multiple Rows

Excel 2010 ... I need to high the highest, second highest & third highest values in column

[SOLVED] Find unique cells in column -> Sum another column based on multiple criteria

[SOLVED] Finding highest value based on criteria

VBA code to find highest and 2nd highest number based in criteria

Find highest based on criteria

Bookmarks

Bookmarks

Posting Permissions