Help----CSE formula tweeking
Posted by Ian on May 16, 2001 12:21 PM
okay this is the formula I am currently using:
=SUM(IF(MOD(ROW(C5:C1765)-ROW(C5),11)=0, C5:C1765))
what I need is for this CSE formula to sum the cells included in the formula only if a cell 6 rows below the one being summed is equal to a certain (text) value. How can I do this?
Thanks in advance,
Ian
Posted by Mark W. on May 16, 2001 1:45 PM
{=SUM(range*(INDEX(OFFSET(range,COUNT(range),),6)="go"))}
...will sum 'range' if 6 cells below contains the word,
"go".
Posted by Mark W. on May 16, 2001 1:51 PM
One additional tweak...
On 2nd thought, the use of the ROWS() rather than
COUNT() will provide a little more data independence.
Please use the following formula instead...
{=SUM(range*(INDEX(OFFSET(range,ROWS(range),),6)="go"))}
Posted by Joe Was on May 16, 2001 1:54 PM
If I understand what you want try:
In cell X6 put:
=IF(X12="text",your SUM formula,"")
I tested it with the formula in G1 and 1 in cells F1:F7 and in G6 I put, text. G1 gave 6 the correct SUM(F1:F6), I used a simple SUM not yours, it should work with yours, just adjust the cell addresses. JSW
Posted by Ian on May 16, 2001 3:07 PM
Not Working for Me
Okay, this is the formula I used
=SUM(C5:C1765*(INDEX(OFFSET(C5:C1765,ROW(C5:C1765),),6)="iCALL 800"))
but it didn't work. I just want to make sure that this formula is doing what I want. I want C5, and every eleveth row after that summed, if and only if the cell 6 rows below C5 (and eleven rows after that for the next one)
I know this makes next to no sense, so I'll explain what it is. It is for billing. We bill by each call, which in the first row of a box for each call, there are the number of minutes for the call. In the sixth row of the box, there is the type of call that we are billing for. What I want is to be able to sum the minutes per call per type of call. Does this make sense?
Ian
Posted by Mark W. on May 16, 2001 3:25 PM
Re: Not Working for Me
Ian, 1 small typo... should be "ROWS(", not "ROW(". Okay, this is the formula I used
Posted by Mark w. on May 16, 2001 3:35 PM
What a minute...I just read your 2nd paragraph...
...your requirement has "evolved" substantially.
Originally, you as to sum the range, C5:C1765, if
the cell 6 rows beneath it contained a significant
text string. Your new specifications will require
some thought... , 1 small typo... should be "ROWS(", not "ROW(". : Okay, this is the formula I used
Posted by Mark W. on May 16, 2001 3:57 PM
Okay, use this...
{=SUM(IF(MOD(ROW(C5:C1765)-ROW(C5),10),0,1)*(OFFSET(C5:C1765,5,)="iCALL 800")*IF(ISNUMBER(C5:1765),C5:C1765))} ...your requirement has "evolved" substantially.
Posted by Mark W. on May 16, 2001 4:32 PM
Still unsure about
..."the 11th row" requirement. You can tweak
the 2nd MOD() argument to fit your needs.
Perhaps, you'll want to use...
{=SUM(IF(MOD(ROW(C5:C1765)-ROW(C5),11),0,1)*(OFFSET(C5:C1765,5,)="iCALL 800")*IF(ISNUMBER(C5:1765),C5:C1765))} {=SUM(IF(MOD(ROW(C5:C1765)-ROW(C5),10),0,1)*(OFFSET(C5:C1765,5,)="iCALL 800")*IF(ISNUMBER(C5:1765),C5:C1765))} : ...your requirement has "evolved" substantially.
Posted by Ian on May 16, 2001 6:57 PM
Re: Still unsure about
I don't think excel likes the ="iCALL 800" part, as it is telling me that there is an error in the formula.
Ian
Posted by Mark W. on May 16, 2001 7:07 PM
Re: Still unsure about
What's the error message? Did you enter it as an array formula using the Control+Shift+Enter
key combination? It's work'n on my test data using "iCALL 800". Did you copy the formula from my posting or retype it yourself? I don't think excel likes the ="iCALL 800" part, as it is telling me that there is an error in the formula.
Posted by Ian on May 17, 2001 9:00 AM
Re: Still unsure about
I copied it from the post, but I removed the outer brackets, and then yes, I used the CSE controls. The error message is the general "your formual contains an error".
By the way, thanks for your help so far.
Ian
What's the error message? Did you enter it as an array formula using the Control+Shift+Enter
Posted by Mark W. on May 17, 2001 9:25 AM
Yippee!! I know what's happening...
This web site is causing my formula to wrap and
may have added other unseen special characters
that Excel cannot handle. I'm afraid that you
will need to carefully type in the formula that
I posted. I copied it from the post, but I removed the outer brackets, and then yes, I used the CSE controls. The error message is the general "your formual contains an error". By the way, thanks for your help so far.
Posted by Mark W. on May 17, 2001 9:31 AM
Re: Yippee!! I know what's happening...
Now, I can really see the problem... The cell range
of the ISNUMBER() argument is clearly wrong! I don't
know how that happened because I pasted it directly
from my working Excel formula. Let's try it again...
and if it's still messed up it definitely something
this web site is doing...
{=SUM(IF(MOD(ROW(C5:C1765)-ROW(C5),10),0,1)*(OFFSET(C5:C1765,5,)="iCALL 800")*IF(ISNUMBER(C5:C1765),C5:C1765))} This web site is causing my formula to wrap and
Posted by Mark W. on May 17, 2001 9:34 AM
Okay, that's much better...
However, the formula is still wrapping in the
middle of the "iCALL 800" string. I recommend that
you copy it from the this posting's "Comments:"
field in the "Post a Followup" section of this
posting. Now, I can really see the problem... The cell range
Posted by Ian on May 17, 2001 3:52 PM
YEAH!!!! PERFECT!!!!
You are the best, Mark. Thanks again
Ian
