Thread: Help with SumProduct

1. Help with SumProduct

Register for a FREE account, and/
or Log in to avoid these ads!

Here is my data table:
htt p://img268.imageshack.us/img268/9551/eventsvh.jpg

And my summary table (on a separate tab):
htt p://img28.imageshack.us/img28/1681/summarye.jpg

You will need to remove the space before the p in 'http' to vist the photo. Must have 5 posts already to post links, but I personally think a visual is very helpful with this question.

Here is my current formula in cells D8:G8:
=SUMPRODUCT(--(Events!\$A:\$A="X"),--((Events!\$T:\$T>0)+(Events!\$V:\$V>0)>0))

The values in Columns T and V of the data pic are quantities.
My current formula is giving me a count of how many Events marked with an X in Column A are shipping product during April. I need to drill that down a bit more. What I need is: in Column D of the summary tab for it to show me how many events with an X in Column A of the events tab are shipping product with a total quantity between Columns T and V of 1-64.

Similarly, I need Column E to show the same thing, but for quantities of 65-96. Column F is 97-192. Column G is 193-576.

Anything above 576 should count more than once in Column G. For example, Row 35 of the Events tab has a total quantity of 2,304. This should count as "4" in Column G. If this last step is going to force me to use macros or make the formula impossible it can be skipped, but it would be helpful.

All of my attempts to accomplish this so far have been met with #VALUE so... help! Thank you to everyone that lends their time to this problem.

~miinstrel

2. =SUMPRODUCT(--(Events!\$A:\$A="X"),--(Events!\$T:\$T+Events!\$V:\$V>=1),--(Events!\$T:\$T+Events!\$V:\$V<=64))
=SUMPRODUCT(--(Events!\$A:\$A="X"),--(Events!\$T:\$T+Events!\$V:\$V>=65),--(Events!\$T:\$T+Events!\$V:\$V<=96))
=SUMPRODUCT(--(Events!\$A:\$A="X"),--(Events!\$T:\$T+Events!\$V:\$V>=97),--(Events!\$T:\$T+Events!\$V:\$V<=192))
=SUMPRODUCT(--(Events!\$A:\$A="X"),--(Events!\$T:\$T+Events!\$V:\$V>=1),--(Events!\$T:\$T+Events!\$V:\$V<=64))
=SUMPRODUCT(--(Events!\$A:\$A="X"),--((Events!\$T:\$T+Events!\$V:\$V>=193)*INT((Events!\$T:\$T+Events!\$V:\$V+384)/576)))

3. Hi Bob -
Thanks for the reply. I've tried the first few formulas you mentioned already (my first attempts). I'm still getting a #VALUE from them. The last formula with the INT function is also still returning a #VALUE.

I've attached a copy of the spreadsheet below... maybe that will be helpful? I'm stumped.

Spreadsheet COPY.xlsx

4. solved via another form. Your formulas work perfectly... I overlooked the fact that there is text on row 4 and it's screwing things up.

5. The problem is that you are trying to add cells that have text in them (T2:T4 and V2:V4).

Change the formula to just uses rows 5:n

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•