You may have additional unseen spaces in your data that prevent a match from
being made.

Try this:

=AVERAGE(IF((TRIM(I1:I200)="Stock")*(K1:K200="x"), L1:L200))
committed with Ctrl+Shift+Enter (Mikes formula)

of

=SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x"),$L$1:$L$200)/SUMPRODUCT(--(TRIM($I$1:$I$200)="stock"),--($K$1:$K$200="x"))

Does that help?

Regards,
Paul


--

"hockeyb9" wrote in message
...
hey guys, i have tried formula both ways.
i hvae rechecked the data to ensure matches - i actually copied the match
cell from data.
still getting the DIV/0!

i really appreciate your help in trying to figure this out because i am
stumped as to why it doesn't work.



"PCLIVE" wrote:

Actually yes. I don't know if something quirky is happening...but all
four
formulas, including yours and the OPs, gives the same result. Should it
not
work?

--

"Mike H" wrote in message
...
Hmmm,

You got his formula to work as posted? For me it produces a value error
as
does your second.

Mike

"PCLIVE" wrote:

What's not working? Though you have an unnecessary ( ), it seems to
work
with your formula or either of the following:

=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"))

=(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" ))))

Your sample data only has one match and therefore is divided by 1.

HTH,
Paul

--

"hockeyb9" wrote in message
...
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