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

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

"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

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" <(E-Mail Removed)> wrote in message
news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
> >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
>
>
>

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

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


"hockeyb9" wrote:

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

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" <(E-Mail Removed)> wrote in message
news:E1AF4000-3551-45F7-9C23-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
>> >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
>>
>>
>>

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

> >> > status financed by days aged
> >> > stock x 10
> >> > customer x 45
> >> > stock y 14
> >> > customer z 80
> >> > customer x 12


Mike


"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" <(E-Mail Removed)> wrote in message
> news:E1AF4000-3551-45F7-9C23-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:E1AF4000-3551-45F7-9C23-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:63B22007-4F0C-4D0A-A20F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E1AF4000-3551-45F7-9C23-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:755DB345-5DCA-432F-9FAB-(E-Mail Removed)...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>