SUMIF - Range is non-contiguous

[redheadpgr]

I'm looking for a solution to a simple problem. I want to sum several non-contiguous cells in a column. Some of the cells, however, have text (usually some text like "SKIP") instead of a number. I have tried using SUMIF like:

=SUMIF((A4,A8,A12,A16),"<>'SKIP'")

This gives error in the cell containing the funtion (the result is usually "#VALUE!". If the range is contiguous, it works find such as:

=SUMIF(A4:A16,"<>'SKIP'")

Any thoughts on this.

 

[sawedoff]

I know this isn't what you wanted to hear, but try
=SUMIF(A4,"<>'skip'")+SUMIF(A8,"<>'skip'")+SUMIF(A12,"<>'skip'")+SUMIF(A16,"<>'skip'")

Of course, if any of the area are contiguous, you could combine them.

An easier way might be to add another sheet, put an IF statement that points to each cell, then sum those cells and transfer the total back to the first sheet.

Sawedoff

 

[dcompto]

If I'm following you correctly, something like this ought to work:

=SUMIF(A1:C5,"<>skip",A1:C5)

 

[KenWright]

If it's only text you are worried about, and you don't have any values between the rows, then just

=SUM(A4:A20)

will work fine.

If you want every 4th one, eg 4,8,12,16, then try

=SUMPRODUCT(--(MOD(ROW($A$4:$A$16),4)=0),($A$4:$A$16))

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------