# 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