Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to perform a looping IF statement across a range of cells and then add the results together without having to write it out manually 40+ times over in the one cell, like below:


All help is greatly appreciated.

share|improve this question

migrated from Jan 23 '13 at 10:52

This question came from our site for professional and enthusiast programmers.

Can't you just do the if statement per cell in another column, fill down and then sum that column? – Jeffrey Theobald Jan 23 '13 at 3:08

If you have Excel 2007 or a later version SUMIFS is the standard function for this, you don't need an "array formula". Try


extend ranges as required

SUMIFS sums one range based on one or more conditions in another range or ranges

If you are using Excel 2003 or earlier you can do the same with SUMPRODUCT, i.e.


share|improve this answer
But array formulae are so much more fun! Good answer, +1 :). – mkingston Jan 23 '13 at 22:40

Enter the following formula:


Now press Ctrl+Shift+Enter. This is called an array formula.

Change the limits of your sum as required.

share|improve this answer
If you have any questions about how or why this works, feel free to ask :) – mkingston Jan 23 '13 at 3:42

You must log in to answer this question.