You are here:
- Home
- Computing/Technology
- Microsoft Software
- Excel
- Excel formula problem
Excel/Excel formula problem
Advertisement
Question
Dear Sirs,
)
I have a long list showing Item No. in A column, Shop No. in B column and Sales Qty. in C column. I just want to create another table which can show easily the sales qty. of those shops by changing the item code in a cell. Please advise what formula should I apply.
Thanks a lot
fkwan
Answer
Hi,
You will need an array formula for this. The formula in cell F5 would be:
=SUM(IF($B$3:$B$15=E5,IF($A$3:$A$15=$F$2,$C$3:$C$15,0),0))
but instead of using Enter, you will need to use Control+Shift+Enter. If done correctly, the formula will have {} brackets around it, indicating it is an array formula. If you edit the formula later, remember to use Contrl+Shift+Enter. You can copy it down like a regular formula.
Doug Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com
Related Articles
- Multiply Excel - How to Multiply in Excel - Multiplying Numbers in Excel
- Add Excel - How to Add in Excel - Adding Numbers in Excel
- Subtract Excel - How to Subtract in Excel - Subtracting Numbers in Excel
- How to Subtract in Excel - Subtract Excel - Subtracting Numbers in Excel
- Excel What If - Answering What If Questions in Excel
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Answers by Expert:
- Nathan Head
- Chad Welch
- Philip Buckley
- Tushar Sakhalkar
- Bhaskar
- Amal
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bob Umlas
- Edward H. Mori
- Jessica L.
- Jan Karel Pieterse
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- Craig
- Adelaide carvalho
- Miguel Zapico
- JP
- Douglas M. Smith
- Richard Rost
- Ken Marron
- Victor Lan
- Bill
- Anne Troy
- Claire Blinman
Douglas M. Smith
Expertise
I specialize in solving formula, feature and structure related problems. I know many tricks to help make your spreadsheets and processing more efficient.
Experience
Brainbench MVP for MS Excel
Past/Present clients
Gannett, Fannie Mae, Pepsi, Nortel, Procter & Gamble, BellSouth, Blue Cross Blue Shield of NC, NC Central University, GlaxoSmithKline, Maintenance Excellence Institute, AAI Pharmaceuticals, Blue Cross Blue Shield of SC, Brainbench.com
©2009 About.com, a part of The New York Times Company. All rights reserved.