average with 2 criteria

[hockeyb9]

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

 

[Mike H]

Try this

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

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

 

[PCLIVE]

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

 

[Mike H]

Hmmm,

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

Mike

 

[hockeyb9]

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]

Hi,

If you got that then I suggest you check your data. It would happen if it
couldn't find a single mtach in columns I & K or the numbers in column L
aren't really numbers.

The usual culprits are rogue spaces. Manually find a match and check all 3
pieces of data so you are 100% sure you have at least 1 match and DIV/0
should go away but you may still have a problem with other data.

Mike

 

[PCLIVE]

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]

The OP's didn't for me. The reason is because I used the data layout as
supplied by the OP and with a header that formula will fail


Mike

 

[hockeyb9]

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]

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


--

 

[PCLIVE]

Good point Mike! I didn't use the headers from the example.



--

 

[smartin]

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

How about a pivot table? Select columns I:L (assuming there is some
extraneous data in J), create the pivot table.

Drag Status to the row area.

Drag Financed By to the column area.

Drag Days Aged to the data area. Double click the button this creates
and change Summarize by: to Average

No muss, no fuss.