Hi,

My brains gone blank... I want to combine a lookup with a sum
function.

A B
Apple 22
Pear 40
Orange 35
Apple 10
Peach 14


So say I wanted the total amount of 'Apple' from column B (which in
this case would be 32).

Thanks

Kestrel


--
kestrel
------------------------------------------------------------------------
kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082
View this thread: http://www.excelforum.com/showthread...hreadid=553552

=SUMIF(A:A,"Apple",B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kestrel" wrote in
message ...

Hi,

My brains gone blank... I want to combine a lookup with a sum
function.

A B
Apple 22
Pear 40
Orange 35
Apple 10
Peach 14


So say I wanted the total amount of 'Apple' from column B (which in
this case would be 32).

Thanks

Kestrel


--
kestrel
------------------------------------------------------------------------
kestrel's Profile:
http://www.excelforum.com/member.php...o&userid=19082
View this thread: http://www.excelforum.com/showthread...hreadid=553552

Thanks, but I have a problem with this formula.

I am using this function to calculate the sums from another
spreadsheet.
The function will work when the other spreadsheet is open, but will not
work when the spreadsheets are closed.


--
kestrel
------------------------------------------------------------------------
kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082
View this thread: http://www.excelforum.com/showthread...hreadid=553552

If you don't want to open the other file, then you can always link the data
in to the current file.

='C:\[YourOtherFilename.xls]Sheet1'!A1

Vaya con Dios,
Chuck, CABGx3



"kestrel" wrote:


Thanks, but I have a problem with this formula.

I am using this function to calculate the sums from another
spreadsheet.
The function will work when the other spreadsheet is open, but will not
work when the spreadsheets are closed.


--
kestrel
------------------------------------------------------------------------
kestrel's Profile: http://www.excelforum.com/member.php...o&userid=19082
View this thread: http://www.excelforum.com/showthread...hreadid=553552

For a closed workbook, try

=SUMPRODUCT(--('workbook_path\[workbook_name.xls]Sheet1'!$A$2:$A$100="Apple"
),'workbook_path\[workbook_name.xls]Sheet1'!$F$4:$F$18)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kestrel" wrote in
message ...

Thanks, but I have a problem with this formula.

I am using this function to calculate the sums from another
spreadsheet.
The function will work when the other spreadsheet is open, but will not
work when the spreadsheets are closed.


--
kestrel
------------------------------------------------------------------------
kestrel's Profile:
http://www.excelforum.com/member.php...o&userid=19082
View this thread: http://www.excelforum.com/showthread...hreadid=553552

Switch to SumProduct if you want to work with a closed workbook:

=SUMPRODUCT(([kestrel.xls]Sheet1!$A$2:$A$6=A2)+0,[kestrel.xls]Sheet1!$B$2:$B$6)

where kestrel.xls is the workbook of interest with data in A2:B2 on
Sheet1 and A2 houses a condition like Apple in the werkbook where you
want to carry the desired calculation.

kestrel wrote:
Thanks, but I have a problem with this formula.

I am using this function to calculate the sums from another
spreadsheet.
The function will work when the other spreadsheet is open, but will not
work when the spreadsheets are closed.