Sum & Lookup function

  • Thread starter Thread starter kestrel
  • Start date Start date
K

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
 
=SUMIF(A:A,"Apple",B:B)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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
 
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)
 
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.
 
Back
Top