Return a cell value based on specific combinations of cells in an array

Hello,

I am working on an Excel project in which I need to return one cell value from an array based on multiple conditions. Let me explain my example as best I can.

I have an array setup with multiple fields such as:
Year , Product, Location, Revenue

What I need to do is return the cell value in the revenue cell where for example Year=2005 & Product=C100 & Location=Atlanta. For each combination like this there is only one possible result to return, however there are multiple records with year = 2005, Product = C100 and so on.

Is there a formula or combination of formulas I can use to return my result?

Thanks so much for your help!

=sumproduct(--(A2:A20=2005),--(B2:B20=C100),--(C2:C20="Atlanta"),D2:D20)

adjust the ranges to suit, or better if you named them

=sumproduct(--(year=2005),--(product=C100),--(location="Atlanta"),revenue)

--
HTH

Bob Phillips

"rmcnam05" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I am working on an Excel project in which I need to return one cell
> value from an array based on multiple conditions. Let me explain my
> example as best I can.
>
> I have an array setup with multiple fields such as:
> Year , Product, Location, Revenue
>
> What I need to do is return the cell value in the revenue cell where
> for example Year=2005 & Product=C100 & Location=Atlanta. For each
> combination like this there is only one possible result to return,
> however there are multiple records with year = 2005, Product = C100 and
> so on.
>
> Is there a formula or combination of formulas I can use to return my
> result?
>
> Thanks so much for your help!
>
>
> --
> rmcnam05
> ------------------------------------------------------------------------
> rmcnam05's Profile:
http://www.excelforum.com/member.php...o&userid=27978
> View this thread: http://www.excelforum.com/showthread...hreadid=474861
>

Hi,

You may also use the following array formula

=sum(if((range1="2005")*(range2="product")*(range3="location"),revenue range))

Regards,

Ashish Mathur

"rmcnam05" wrote:

>
> Hello,
>
> I am working on an Excel project in which I need to return one cell
> value from an array based on multiple conditions. Let me explain my
> example as best I can.
>
> I have an array setup with multiple fields such as:
> Year , Product, Location, Revenue
>
> What I need to do is return the cell value in the revenue cell where
> for example Year=2005 & Product=C100 & Location=Atlanta. For each
> combination like this there is only one possible result to return,
> however there are multiple records with year = 2005, Product = C100 and
> so on.
>
> Is there a formula or combination of formulas I can use to return my
> result?
>
> Thanks so much for your help!
>
>
> --
> rmcnam05
> ------------------------------------------------------------------------
> rmcnam05's Profile: http://www.excelforum.com/member.php...o&userid=27978
> View this thread: http://www.excelforum.com/showthread...hreadid=474861
>
>