Using a range of functions

**Shazaam** · 01-25-2008, 04:18 PM

I want to search a row of cells for a certain text value. Each time the value is found, I want to return a number value from that column, then sum those numbers.

For instance, in the table below, I want to sum the numbers from row 1 of each column with a 'yes' in row 2:

[423] [123] [412]
[yes ] [ no ] [yes]

I want the function to return [835] from this table.
I can do this currently with the following formula:
=SUM( IF(A2="yes", A1, 0) , IF(B2="yes", B1, 0), IF(C2="yes", C1, 0) )

My problem is that I want to make this function work for columns A thru AM without having to type out such a long formula. Is it possible to perform IF (or another appropriate function) on a range of cells (similar to using SUM(A1:A3) to add all the values of A1, A2, and A3)?

If not, is there another solution that would work for this situation?

**cmcconnehey** · 01-25-2008, 04:36 PM

You should be able to use the "SumIf" function so that it only adds those that have the word "yes" below them.

Below is the formula that would yield the result 835 in the example you provided given the data was in rows 1 and 2.

HTML Code: