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
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