# How do I iterate an IF statement over every cell in a range in Excel?

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.

-

## migrated from stackoverflow.comJan 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

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.

-
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))`

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