i tried this and got the dreaded #DIV/0!
i did enter as an array.

any idea - in my real data i am matching to an actual cell. ie) instead of
"stock" it's $c$5 so that i can have the same formula for multiple matches.

thanks for your help mike.



"Mike H" wrote:

Try this

=AVERAGE(IF((I1:I200="Stock")*(K1:K200="x"),L1:L20 0))

Enter as an array by pressing CTRL+Shift+Enter NOT just Enter. If you do it
correctly then Excel will put curly brackets around it {}. You can't type
these yourself.

Mike

"hockeyb9" wrote:

i am trying to set up an average days for inventory based on two criteria.
i have set up a calculation to get the number of days aged already, but
can't get a sumproduct formula to work.

ie) columns
i k L
status financed by days aged
stock x 10
customer x 45
stock y 14
customer z 80
customer x 12

i tried

=(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x ")*($L$1:$L$200))/(SUMPRODUCT(($I$1:$I$200="stock")*($K$1:$K$200="x" ))))

to no avail.
anyone have an idea / solution?
thanks