Excel "If" Function formula

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 14:16:46 CDT 2008

Assuming the sheet names are in A1 down, this will sum the id 1's on the
sheet in A1

SUMPRODUCT(--(INDIRECT("'"&A1&"'!A1:A10")=1),INDIRECT("'"&A1&"'!B1:B10"))

--
---
HTH

Bob


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



"Jennifer" <Jennifer@discussions.microsoft.com> wrote in message
news:C464B60A-6F4C-4A23-9C2D-A2DF61F82D0B@microsoft.com...
> 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 14:30:00 CDT 2008

To be honest I'm not totally sure if this is what you asked, but the names
and IDS are listed in a sheet called "paddle owners" and the sales (including
the identifying ID if they purchased and the sale price) is called "sale
record".

What I need is a formula (for the paddle owner sheet) that says "If ID 1
from "paddle owner sheet, column A" appears in "sale record sheet, column A"
sum all sale prices from "sale record sheet column E". Does that make sense?

I'll try your formula now.

"Bob Phillips" wrote:

> Assuming the sheet names are in A1 down, this will sum the id 1's on the
> sheet in A1
>
> SUMPRODUCT(--(INDIRECT("'"&A1&"'!A1:A10")=1),INDIRECT("'"&A1&"'!B1:B10"))
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "Jennifer" <Jennifer@discussions.microsoft.com> wrote in message
> news:C464B60A-6F4C-4A23-9C2D-A2DF61F82D0B@microsoft.com...
> > 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
>
>
>