Sum & Lookup function

[kestrel]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[kestrel]

Thanks, but I have a problem with this formula.

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

 

[CLR]

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

 

[Bob Phillips]

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)

 

[Aladin Akyurek]

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.