SUMPRODUCT with embedded MAX/MEDIAN by Groups

Hi Folks -- good evening!

I have a spreadsheet with 5 columns: Month, Distro Partner, Product, Title, and Sales

I need (was using sumproduct formula) to calculate: number of Titles (by product), total sales volume, average sales by product, title with highest cumulative sales across all months, and median sales number by product
*BUT* the calculations should only include TITLES with a SUM TOTAL over 500 cumulative sales across all months (not sales over 500 in one month)


I thought my SUMPRODUCT formula was working until I realized that it was not taking the sum total of each title across all months (multiple instances: one instance for each month). I need a formula that will allow me to find the SUMPRODUCT/MAX/MEDIAN of total sales for each title regardless of month.



=SUMPRODUCT(($J3=$C$2:$C$25)*(J$2=$B$2:$B$25)*($E$2:$E$25>=500),$E$2:$E$25)


Please see the attached file

This array formula (activated with Ctrl+Shift+Enter) will find the Max value:

Please Login or Register  to view this content.

This array formula will find the median value:

Please Login or Register  to view this content.

Here is a copy of your file with the formulas applied:
Copy of Sumproduct Groupings.xlsx
Let me know if you have any questions.

Originally Posted by collegeitdept

In addition to looking at the Product names, it needs to calculate at the Title level. So does each Title (across all month instances) sum total over 500.

I think that I have an idea of what you mean but it would help to see a sample of this table.

Originally Posted by collegeitdept

Please see the modified file. The correct values (what the formula should return) is in the table on the right in red text.

I don't understand where those numbers come from. Take the value of 2 in cell O3 I would assume that to be the count of "Distributor Online" that is made up of "News Now". However looking at the data in columns B and C the count seems as if it should be 6. I will need to understand where the red numbers come from in order to write the formulas.

I understand what you are looking for now. I want to show you a pivot table solution that partially supplies the information that you are looking for, albeit not in the form that you requested. The highlighted titles are those that have sales of more than 500 total. The 'Median' column is not actually part of the pivot table but the formula is copied down far enough to accommodate any of the three Distro Partners individually. Distro Partners can be selected in P10, the selection of more than one will temporarily disable the 'Median' column.
Copy of Sumproduct Groupings.xlsx
What you are looking for is probably doable, and if this doesn't work for you I'll come back to the problem later.
Let me know if you have any questions.