| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a formula
I would like to create a formula in one cell that would calculate amounts in
a given range. For example: Column A Column B Apples $10 Oranges $20 Bananas $15 Apples $10 Pears $50 Apples $10 From the chart above, I can see that the amount spent on Apples was $30. I would like to create a formula that would lookup Apples in column A and total the corresponding amount from column B (without having to sort column A). Any help is greatly appreciated. Thank you! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating a formula
=SUMPRODUCT(--(A1:A100="Apples"),--(B1:B100))
-- Brevity is the soul of wit. "WendyBTR8860" wrote: I would like to create a formula in one cell that would calculate amounts in a given range. For example: Column A Column B Apples $10 Oranges $20 Bananas $15 Apples $10 Pears $50 Apples $10 From the chart above, I can see that the amount spent on Apples was $30. I would like to create a formula that would lookup Apples in column A and total the corresponding amount from column B (without having to sort column A). Any help is greatly appreciated. Thank you! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating a formula
=SUMIF(A:A,"Apples",B:B)
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "WendyBTR8860" wrote in message ... I would like to create a formula in one cell that would calculate amounts in a given range. For example: Column A Column B Apples $10 Oranges $20 Bananas $15 Apples $10 Pears $50 Apples $10 From the chart above, I can see that the amount spent on Apples was $30. I would like to create a formula that would lookup Apples in column A and total the corresponding amount from column B (without having to sort column A). Any help is greatly appreciated. Thank you! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating a formula
=SUM((A1:A100="Apples")*(B1:B100))
ctrl+shift+enter, not just enter "WendyBTR8860" wrote: I would like to create a formula in one cell that would calculate amounts in a given range. For example: Column A Column B Apples $10 Oranges $20 Bananas $15 Apples $10 Pears $50 Apples $10 From the chart above, I can see that the amount spent on Apples was $30. I would like to create a formula that would lookup Apples in column A and total the corresponding amount from column B (without having to sort column A). Any help is greatly appreciated. Thank you! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating a formula that exceeds 1,000 characters | Excel Worksheet Functions | |||
| Excel displaying formulae as constant and not calculating formula | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula for Calculating a Floating Date | Excel Worksheet Functions | |||
Linear Mode
