# Conditional SUMPRODUCT

1. ## Conditional SUMPRODUCT

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

3. ## Re: Conditional SUMPRODUCT

1 quick observation - using SP on that many rows is probably slowing your file way down. It may be better to use helper columns to run intermediate calcs, and then use a less resource-intense function.

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

4. ## Re: Conditional SUMPRODUCT

Hi FDibbins,

I'm not able to attach directly. The file is 9MB

5. ## Re: Conditional SUMPRODUCT

we only need a small sample of what you are working with, just enough so we can give you want you need

6. ## Re: Conditional SUMPRODUCT

Got it... I shortened the raw data. And also compressed it.

But I was not able edit the original post... So I was forced to repost the question:

http://www.excelforum.com/excel-form...-attached.html

Thanks

7. ## Re: Conditional SUMPRODUCT

Unable to open the file

You can just upload the file here, in a new post

8. ## Re: Conditional SUMPRODUCT

Hi FDibbins,

I did. I uploaded the file directly to the forum in a new post:

http://www.excelforum.com/excel-form...-attached.html

Thanks

9. ## Re: Conditional SUMPRODUCT

Yes, I know and I am unable to open that file

11. ## Re: Conditional SUMPRODUCT

I reposed a newly created zip file

http://www.excelforum.com/excel-form...-attached.html

12. ## Re: Conditional SUMPRODUCT

The link is not broken, I can d/l the zip file, butthe zip file had an error when opening it

Also, you need to try and upload future files on this thread here, not the otehr thread

13. ## Re: Conditional SUMPRODUCT

I was able to open that 1.

Instead of SP, you should take a look at using the xxxIFs() range of functions, they are far more efficient than SP
=SUMPRODUCT(('Raw Data'!\$E\$2:\$E\$30894=\$K9)*('Raw Data'!\$C\$2:\$C\$30894=L\$8)*('Raw Data'!\$H\$2:\$H\$30894>=\$K\$8))
I used...
=COUNTIFS('Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)

You could do the sane for SUM...
=SUMIFS('Raw Data'!\$H:\$H,'Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)
and average...
=AVERAGEIFS('Raw Data'!\$H:\$H,'Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)

Other than that, not really sure what you wanted here?

14. ## Re: Conditional SUMPRODUCT

Thanks FDibbins, but How would I calculate the Max & median?

Thanks

15. ## Re: Conditional SUMPRODUCT

Originally Posted by FDibbins
I was able to open that 1.

Instead of SP, you should take a look at using the xxxIFs() range of functions, they are far more efficient than SP
=SUMPRODUCT(('Raw Data'!\$E\$2:\$E\$30894=\$K9)*('Raw Data'!\$C\$2:\$C\$30894=L\$8)*('Raw Data'!\$H\$2:\$H\$30894>=\$K\$8))
I used...
=COUNTIFS('Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)

You could do the sane for SUM...
=SUMIFS('Raw Data'!\$H:\$H,'Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)
and average...
=AVERAGEIFS('Raw Data'!\$H:\$H,'Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)

Other than that, not really sure what you wanted here?

Hi FDibbins,

the formula:
=SUMIFS('Raw Data'!\$H:\$H,'Raw Data'!\$E:\$E,TABLE!\$K9,'Raw Data'!\$C:\$C,TABLE!L\$8,'Raw Data'!\$H:\$H,">="&TABLE!\$K\$8)

is incorrect because it still suffers from the original problem with the SUMPRODUCT function. It's only including titles with sales over 500 - excluding instances where a title has sales under 500 in a particular month. In reality, even if a title had sales of 300, cumulative sales in 3 months could be well over 500. The qualifier should be cumulative sales over 500 for each title.

Thank you

Page 1 of 2 1 2 Last

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