MeatLightning wrote:
Ok... this might be impossible or otherwise crazy... but I'll ask anyway:

Is there a way to calculate standard deviation where only certain qualifying
data is analyzed?

I have a bunch of data... For example:

orderID Order $ Date # of Parts
w1234 $5 1/1/09 10
w1235 $10 1/1/09 5
w1236 $7 1/4/09 10

etc, etc (I have like 9k rows and my real data has more columns)

Currently I chew through this data using SUMPRODUCT to pull together
different groups. For example:
- Show me total order $ for "w1234" in 2009.
- Show me # of orders for "w1234" in 2009.
- Show me Avg. order $ for "w1234" in 2009.

My question is: Is there a way to calculate standard deviation in a similar
way? For example: Show me standard deviation of order $ for "w1234" in 2009.

Agreed SUMPRODUCT is great for sifting through multiple criteria, but
only to produce counts and sums.

Array formulae can take care of all of your needs in this case:
=STDEV(IF(($A$2:$A$11="w1234")*(YEAR($C$2:$C$11)=2 009),$B$2:$B$11))

Array formulae need to be committed by pressing Ctrl+Shift+Enter (not
just enter).

Substitute SUM, COUNT, or AVERAGE for the other three metrics. I'm
guessing with 9k rows this shouldn't be too slow.