Hello,

I have a worksheet with over 200K rows/data entries. This worksheet contains a list of titles and its sales performance by month - over a three month period. Not all titles have sales in each of three months, some had sales in 2 months or only 1.

The worksheet lists the sales for each title by distributor by month.


I need a "master" sumproduct function to get the SUM total value of all sales during the 3-month period *BUT* for only titles that had *cumulative* sales over 500.

Here is my formula that needs adjusting:

=SUMPRODUCT(('Raw Data'!$E$2:$E$228707=TITLE)*('Raw Data'!$C$2:$C$228707=DISTRIBUTOR)*('Raw Data'!$H$2:$H$228707>=500),'Raw Data'!$H$2:$H$228707)

Unfortunately that formula "filters" individual titles with sales over 500 by individual month, and NOT the titles cumulative sales over 3 months that exceed 500.


Once I have that conditional sumproduct formula.... I'll need another formula to calculate the average sales performance of titles with cumulative sales over 500. I'm assuming all I need to calculate the average is a DISTINCT/count Unique values function.


IE:
SUMPRODUCT/Unique values




I'll then need a formula to calculate the MAX/HIGHEST cumulative value. IE: what was the highest MAX value of cumulative sales (title with the highest sales over 3 months).

=MAX(IF(('Raw Data'!$E$2:$E$228707=TITLE)*('Raw Data'!$C$2:$C$228707=DISTRIBUTOR)*('Raw Data'!$H$2:$H$228707>=500),'Raw Data'!$H$2:$H$228707))

again this formula is incorrect because it only looks for the individual highest value listing, and not the highest cumulative total value


Thanks