Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

=IF(AND(F17=N4,G17=5),H17,0)+IF(AND(F18=N4,G18=5),H18,0)+IF(AND(F19=N4,G19=5),H19,0)...

All help is greatly appreciated.

share|improve this question

migrated from stackoverflow.com Jan 23 '13 at 10:52

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

3  
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

2 Answers 2

Enter the following formula:

=SUM((F17:F19=$N$4)*(G17:G19=5)*(H17:H19))

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

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

=SUMIFS(H17:H57,F17:F57,N4,G17:G57,5)

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.

=SUMPRODUCT(H17:H57,(F17:F57=N4)*(G17:G57=5))

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.