Making this formula work

Based on a question in a different forum, I was trying to see if I could
figure out the answer using some of the new techniques I've seen on these
forums. I wrote the following formula, which I can't seem to get to work,
although using F9 to calculate parts of the formula seem to indicate it
should work. Can someone point me in the right direction, assuming what I am
trying is possible.
My formula is:
=SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))

In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
respectively.

CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
{"G10","J10","M10","P10"}.

Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
{100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I enter
this formula in another cell, I get 1000. However, if I use the original
formula, no matter whether I enter it normally or as an array formula, I get
100. What's up with that?

This appears to be the step that it is failing on as it returns 100, but I
don't know how to fix it or if it is doable:
=SUM(INDIRECT({"g10","j10","m10","p10"}))

Can anyone shed any light as to if this is doable like this and if so, what
I need to change in order to make it work? Thanks.

Use

=SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))

or if you just want to sum every third cell from G10 to P10

=SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)



--

Regards,

Peo Sjoblom

"Kleev" <Kleev@discussions.microsoft.com> wrote in message
news:66B9C5D3-BD30-4F90-B0D8-839432CFAFFD@microsoft.com...
> Based on a question in a different forum, I was trying to see if I could
> figure out the answer using some of the new techniques I've seen on these
> forums. I wrote the following formula, which I can't seem to get to work,
> although using F9 to calculate parts of the formula seem to indicate it
> should work. Can someone point me in the right direction, assuming what I
am
> trying is possible.
> My formula is:
> =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
>
> In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
> respectively.
>
> CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
> {"G10","J10","M10","P10"}.
>
> Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
> {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I
enter
> this formula in another cell, I get 1000. However, if I use the original
> formula, no matter whether I enter it normally or as an array formula, I
get
> 100. What's up with that?
>
> This appears to be the step that it is failing on as it returns 100, but I
> don't know how to fix it or if it is doable:
> =SUM(INDIRECT({"g10","j10","m10","p10"}))
>
> Can anyone shed any light as to if this is doable like this and if so,
what
> I need to change in order to make it work? Thanks.

Thank you very much. You answered my question, and I was able to adapt one
of your solutions (after much time and toil) to do what I had originally set
out to do. However, I don't think my answer ends up being any better than
what the OP on the other forum started with, so will not post this answer
there. But what I finally came up with is:

=IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0))=0,0,
SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4,
N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")),
0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0)))

Probably could be much simplified, but I feel lucky to have gotten this to
work.


"Peo Sjoblom" wrote:

> Use
>
> =SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))
>
> or if you just want to sum every third cell from G10 to P10
>
> =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "Kleev" <Kleev@discussions.microsoft.com> wrote in message
> news:66B9C5D3-BD30-4F90-B0D8-839432CFAFFD@microsoft.com...
> > Based on a question in a different forum, I was trying to see if I could
> > figure out the answer using some of the new techniques I've seen on these
> > forums. I wrote the following formula, which I can't seem to get to work,
> > although using F9 to calculate parts of the formula seem to indicate it
> > should work. Can someone point me in the right direction, assuming what I
> am
> > trying is possible.
> > My formula is:
> > =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
> >
> > In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
> > respectively.
> >
> > CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
> > {"G10","J10","M10","P10"}.
> >
> > Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
> > {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I
> enter
> > this formula in another cell, I get 1000. However, if I use the original
> > formula, no matter whether I enter it normally or as an array formula, I
> get
> > 100. What's up with that?
> >
> > This appears to be the step that it is failing on as it returns 100, but I
> > don't know how to fix it or if it is doable:
> > =SUM(INDIRECT({"g10","j10","m10","p10"}))
> >
> > Can anyone shed any light as to if this is doable like this and if so,
> what
> > I need to change in order to make it work? Thanks.
>
>
>

On second thought, since you wouldn't be able to copy that and have it change
based on what row you were on, I made the following modification to it.

=IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4, 1, 0))=0,0,
SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4,
N(INDIRECT(CHAR({7,10,13,16} + 64) & ROW())),
0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4, 1, 0)))

"Kleev" wrote:

> Thank you very much. You answered my question, and I was able to adapt one
> of your solutions (after much time and toil) to do what I had originally set
> out to do. However, I don't think my answer ends up being any better than
> what the OP on the other forum started with, so will not post this answer
> there. But what I finally came up with is:
>
> =IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0))=0,0,
> SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4,
> N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")),
> 0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0)))
>
> Probably could be much simplified, but I feel lucky to have gotten this to
> work.
>
>
> "Peo Sjoblom" wrote:
>
> > Use
> >
> > =SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))
> >
> > or if you just want to sum every third cell from G10 to P10
> >
> > =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)
> >
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom

I believe your CHAR function is returning an array. If you highlite 4
adjacent cells when you type your formla and then enter it with
control-shift-enter you will get 100, 200, 300, and 400 in those 4 cells. If
you want the answer in just one cell I think you will have to make one
indirect formula for each cell you are adding.

Try this formula:
=SUM(INDIRECT(CHAR(71)&"10"),INDIRECT(CHAR(74)&"10"),INDIRECT(CHAR(77)&"10"),INDIRECT(CHAR(80)&"10"))

Hope this helps.

Thanks,
Bill Horton

"Kleev" wrote:

> Based on a question in a different forum, I was trying to see if I could
> figure out the answer using some of the new techniques I've seen on these
> forums. I wrote the following formula, which I can't seem to get to work,
> although using F9 to calculate parts of the formula seem to indicate it
> should work. Can someone point me in the right direction, assuming what I am
> trying is possible.
> My formula is:
> =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
>
> In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
> respectively.
>
> CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
> {"G10","J10","M10","P10"}.
>
> Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
> {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I enter
> this formula in another cell, I get 1000. However, if I use the original
> formula, no matter whether I enter it normally or as an array formula, I get
> 100. What's up with that?
>
> This appears to be the step that it is failing on as it returns 100, but I
> don't know how to fix it or if it is doable:
> =SUM(INDIRECT({"g10","j10","m10","p10"}))
>
> Can anyone shed any light as to if this is doable like this and if so, what
> I need to change in order to make it work? Thanks.