Formula Not Working
 

{=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!

Formula Not Working
 

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



"JBoyer" wrote:

{=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!

Formula Not Working
 

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" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
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(I F(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 !
--------------------------------------------------------------------------
-


"JBoyer" wrote in message
...


{=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!

Formula Not Working
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JBoyer" wrote in message
...
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" wrote:

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

--
HTH,

RD


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

--------------------------------------------------------------------------
-
"Ragdyer" wrote in message
...
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(I F(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 !

--------------------------------------------------------------------------
-


"JBoyer" wrote in message
...



{=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!