Monday, February 27, 2006
SUMPRODUCT array formulas in Excel
I have written about these before as a way of summing data from tables using multiple selection criteria.
SUM(range) just sums a range of cells.
SUMIF(criteria range, criteria, sum range) sums data in one range based on results in a parallel range.
SUMPRODUCT((array1="criteria")*(array2="criteria")*(array3)) entered with CTRL SHIFT ENTER returns the sum of the data in the array3 where the crietria in arrays one and two are met. This can be expanded a lot.
Chris Pearson (as always) is one of the definitive sources for array forumlas.
Bob Phillips from the UK has written a very detailed page that very carefully explains how this works and how to use it with some practical examples.
Reading them both will give you some very valuable insights into how array formulas work and how to utilise them in your spreadsheets.
Some additional tips.
- Arrays must be the same dimensions.
- Having invalid information (eg text) in the array can be ignored using the double unary operator like so SUMPRODUCT(--(array1="criteria")*(array2="criteria")*(array3)).
- Arrays cannot be a whole column, but can be a whole row.
Finally, if anybody knows anything about how Office 12 will treat these types of formulas I am sure the Excel community would be interested to know.
The Excel Guru's Free Tips and Hints.
Welcome
Welcome to the Excel Tips blog. Free Microsoft Excel Tips and Hints designed to increase your productivity.
Advertisement
Subscribe Me
Previous Posts
- Urgent Request
- Resetting the last cell in Excel
- Using theTranspose function in Excel
- Excel MVP and Professional Guru association ideas
- Debugging VBA
- De-Lurking Week
- Zooming an Excel spreadsheet for named ranges
- Reading complex formulas in Excel
- CTRL SHIFT ENTER Formulas in Excel
- Using the CTRL Key
My Blogs and Websites
- Spy Journal Home
- Spy Journal Archives
Personal Blog- Excel Tips Blog
- Blog Tips Blog
- Tech Tips Blog
- Urban Space Novel
- Photo Archive
- Parklife Results
- Parklife Soccer Club Website
- KROSTech LAN Parties
- Jethro Management
- Jethro Consultants
- Oz Bush Poet
Excel Links
- ---RSS Feeds---
- Unofficial Microsoft Office Stuff
- KC on Exchange and Outlook
- Office Zealot
- Automate Excel
- Colo's Excel Junk room
- Daily Dose Of Excel
- The Planning Deskbook
- Excel Pragma
- Andrew's Excel Tips
- ---No RSS Feed---
- Excel Tip
- Mailbarrow
- Experts Exchange
- AJP Excel Information
- Beyond Technology
- Contextures
- ExcelTip
- Mr Excel
- Pearson Software Consulting
- Peltier Technical Services
- John Walkenbach - J-Walk
- Vangelder (wont work in Firefox)
- VBA Express
- xlDynamic
- The Excel Nexus
- Code Net
- Better Solutions
- Excel Business Tools
- Data Pig Technologies
- VBA Express
Other Links
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.
Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.