# Conditional Sumproduct (with file attached)

1. ## Conditional Sumproduct (with file attached)

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.

attached file:

Marketing - Sales by Poster Titles and Distributor.zip

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

2. ## Re: Conditional Sumproduct (with file attached)

There are currently 1 users browsing this thread. (0 members and 1 guests)