IF Function formula

I posted this in the general questions but it's better suited for here:

Hi I need some help figuring out if it is possible to create a formula.

I'm trying to link information from different pages in a spreadsheet that
contain id's for people and prices they are paying for some items my church
is selling. We have a main worksheet that lists their names and the ids and
then we have another worksheet that lists all the items they can buy with
it's price.

Is it possible to create a formula in the name page that says "If you find
id #1 in column A, then sum the prices from column D". The ids will be spread
out throughout column A so we need a formula to locate each instance of the
id and then grab the price listed each time in column D and sum those prices
up.

Is this possible and if yes can someone tell me how to do it.

Thanks

BillyLiddel
Sat May 10 15:31:01 CDT 2008

Jennifer, try this

=SUMPRODUCT(--('Sales record'!$A$2:$A$30='Paddle owners'!A2)*('Sales
record'!$D$2:$D$30))

enter this on the paddle owners sheet in row 2 and copy down. Change the
range to suit just make sure that the both columns, A & D have the same
number of rows.

Regards
Peter

"Jennifer" wrote:

> I posted this in the general questions but it's better suited for here:
>
> Hi I need some help figuring out if it is possible to create a formula.
>
> I'm trying to link information from different pages in a spreadsheet that
> contain id's for people and prices they are paying for some items my church
> is selling. We have a main worksheet that lists their names and the ids and
> then we have another worksheet that lists all the items they can buy with
> it's price.
>
> Is it possible to create a formula in the name page that says "If you find
> id #1 in column A, then sum the prices from column D". The ids will be spread
> out throughout column A so we need a formula to locate each instance of the
> id and then grab the price listed each time in column D and sum those prices
> up.
>
> Is this possible and if yes can someone tell me how to do it.
>
> Thanks

Jennifer
Sat May 10 17:55:00 CDT 2008

This is PERFECT! Thank you!

"Billy Liddel" wrote:

> Jennifer, try this
>
> =SUMPRODUCT(--('Sales record'!$A$2:$A$30='Paddle owners'!A2)*('Sales
> record'!$D$2:$D$30))
>
> enter this on the paddle owners sheet in row 2 and copy down. Change the
> range to suit just make sure that the both columns, A & D have the same
> number of rows.
>
> Regards
> Peter
>
> "Jennifer" wrote:
>
> > I posted this in the general questions but it's better suited for here:
> >
> > Hi I need some help figuring out if it is possible to create a formula.
> >
> > I'm trying to link information from different pages in a spreadsheet that
> > contain id's for people and prices they are paying for some items my church
> > is selling. We have a main worksheet that lists their names and the ids and
> > then we have another worksheet that lists all the items they can buy with
> > it's price.
> >
> > Is it possible to create a formula in the name page that says "If you find
> > id #1 in column A, then sum the prices from column D". The ids will be spread
> > out throughout column A so we need a formula to locate each instance of the
> > id and then grab the price listed each time in column D and sum those prices
> > up.
> >
> > Is this possible and if yes can someone tell me how to do it.
> >
> > Thanks

Bob
Sat May 10 18:55:10 CDT 2008

No need for -- and *, you are doing the same job twice.

Either

=SUMPRODUCT(--('Sales record'!$A$2:$A$30='Paddle owners'!A2),--('Sales
record'!$D$2:$D$30))

or

=SUMPRODUCT(('Sales record'!$A$2:$A$30='Paddle owners'!A2)*('Sales
record'!$D$2:$D$30))

is sufficient

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Billy Liddel" <BillyLiddel@discussions.microsoft.com> wrote in message
news:FB495F00-BEA7-46D8-89D3-CF67F27CE35E@microsoft.com...
> Jennifer, try this
>
> =SUMPRODUCT(--('Sales record'!$A$2:$A$30='Paddle owners'!A2)*('Sales
> record'!$D$2:$D$30))
>
> enter this on the paddle owners sheet in row 2 and copy down. Change the
> range to suit just make sure that the both columns, A & D have the same
> number of rows.
>
> Regards
> Peter
>
> "Jennifer" wrote:
>
>> I posted this in the general questions but it's better suited for here:
>>
>> Hi I need some help figuring out if it is possible to create a formula.
>>
>> I'm trying to link information from different pages in a spreadsheet that
>> contain id's for people and prices they are paying for some items my
>> church
>> is selling. We have a main worksheet that lists their names and the ids
>> and
>> then we have another worksheet that lists all the items they can buy with
>> it's price.
>>
>> Is it possible to create a formula in the name page that says "If you
>> find
>> id #1 in column A, then sum the prices from column D". The ids will be
>> spread
>> out throughout column A so we need a formula to locate each instance of
>> the
>> id and then grab the price listed each time in column D and sum those
>> prices
>> up.
>>
>> Is this possible and if yes can someone tell me how to do it.
>>
>> Thanks