Formula Not Working

[JBoyer]

{=IF(ISBLANK(INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21)),"",SUM(IF(F31:L31<0,INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21),FALSE)))}

{=IF(ISBLANK(INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21)),"",SUM(IF((INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21))<0,INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21),FALSE)))}

The first formula works but it is not really what I want to do, when I
change it to the second formula which is what I want it to do it gives me a
#VALUE! ERROR.

This is the only part that changes:

F31:L31<0 changes to (INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21))<0

Hope someone can give me a solution to fix this problem! Thanks in advance!

 

[daddylonglegs]

Perhaps better to describe what you want the formula to do....

Assuming that you have this formula first in C2 and that it should sum the
negative values in F31:L31...then C3 should sum negative values in
F36:L36.....etc.

Try this formula for C2 copied down

=SUMIF(OFFSET(F$26:L$26,ROWS(C$2:C2)*5,0),"<0")

 

[Ragdyer]

If you can live with a zero return if the range is empty OR if there are no
negative values, try this formula, which can be entered *anywhere*, and
copied down as needed:
It's set to start at Row 31,

=SUMIF(INDEX(F:L,ROWS($1:2)*5+21,),"<0")

If you want something similar to your original configuration, try this,
which also can be entered anywhere and copied down as needed, after the CSE
entry:

=IF(COUNTA(INDEX(F:L,ROWS($1:2)*5+21,))=0,"",SUM(IF(INDEX(F:L,ROWS($1:2)*5+2
1,)<0,INDEX(F:L,ROWS($1:2)*5+21,))))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

{=IF(ISBLANK(INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21)),"",SUM(IF(F31:L31<0,I
NDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21),FALSE)))}
{=IF(ISBLANK(INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21)),"",SUM(IF((INDIRECT("
F"&ROW()*5+21&":L"&ROW()*5+21))<0,INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21),F
ALSE)))}

The first formula works but it is not really what I want to do, when I
change it to the second formula which is what I want it to do it gives me a
#VALUE! ERROR.

This is the only part that changes:

F31:L31<0 changes to (INDIRECT("F"&ROW()*5+21&":L"&ROW()*5+21))<0

Hope someone can give me a solution to fix this problem! Thanks in

advance!

 

[Ragdyer]

Actually, this NON-array and NON-volatile formula should follow your
original concept, and still be able to be entered anywhere.

Again, set to start at Row 31:

=IF(COUNTA(INDEX(F:L,ROWS($1:2)*5+21,))=0,"",SUMIF(INDEX(F:L,ROWS($1:2)*5+21
,),"<0"))

 

[JBoyer]

Thank you so much! Your formula is much simpler than mine and does the same
trick. It is exactly what I was looking for. Sorry if my description was
vague, but I'm glad you were able to figure out what I wanted.

 

[Ragdyer]

You're welcome, and thanks for the feed-back.