Prev: Migrating to something else
Next: Cell Referencing
From: George A. Jululian on 10 Apr 2010 02:03
Please help in this formula

{=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
1-3'!$G$3:$G$19268=$H3)*'[Summary1.xls]A1 1-3'!$S$3:$Y$19268)}
the result should read 24 anf it give 0

please advise
regards
From: JLatham on 10 Apr 2010 02:20
If you've copied the formula correctly, it looks to me like you are missing a
( just after the last *.
And in the last reference, you show $S$3:$Y$19268 - the $Y should be $S ??

formula should read
=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
1-3'!$G$3:$G$19268=$H3)*('[Summary1.xls]A1 1-3'!$S$3:$S$19268)
and of course, then [Ctrl]+[Shift]+[Enter] to make it an array formula again.


"George A. Jululian" wrote:

> Please help in this formula
>
> {=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
> 1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
> 1-3'!$G$3:$G$19268=$H3)*'[Summary1.xls]A1 1-3'!$S$3:$Y$19268)}
> the result should read 24 anf it give 0
>
> please advise
> regards
From: JLatham on 10 Apr 2010 02:21
Also, it could be rewritten as a non-array formula using SUMPRODUCT()

=SUMPRODUCT(--('[Summary1.xls]A11-3'!$C$3:$C$19268=B$11),--('[Summary1.xls]A11-3'!$E$3:$E$19268=$A9),--('[Summary1.xls]A11-3'!$G$3:$G$19268=$H3),--('[Summary1.xls]A11-3'!$S$3:$S$19268))


"George A. Jululian" wrote:

> Please help in this formula
>
> {=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
> 1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
> 1-3'!$G$3:$G$19268=$H3)*'[Summary1.xls]A1 1-3'!$S$3:$Y$19268)}
> the result should read 24 anf it give 0
>
> please advise
> regards
From: George A. Jululian on 10 Apr 2010 03:09
many thanks on your replies why should i replace its with S and i went it Y

please advice

regards

"George A. Jululian" wrote:

> Please help in this formula
>
> {=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
> 1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
> 1-3'!$G$3:$G$19268=$H3)*'[Summary1.xls]A1 1-3'!$S$3:$Y$19268)}
> the result should read 24 anf it give 0
>
> please advise
> regards
From: JLatham on 10 Apr 2010 13:28
If your formula actually works, then go ahead and mix S/Y. But typically a
formula like this looks for matching number of cells in each part of it. It
may actually work in your setup - without seeing it and testing, I can't be
sure.

I mentioned it because it was not a standard setup like I'm used to seeing.
But I haven't seen everything and it may work just fine as you have it once
you fix the missing ( problem. If it does work then, just ignore my comment
about S/Y.

"George A. Jululian" wrote:

> many thanks on your replies why should i replace its with S and i went it Y
>
> please advice
>
> regards
>
> "George A. Jululian" wrote:
>
> > Please help in this formula
> >
> > {=SUM(('[Summary1.xls]A1 1-3'!$C$3:$C$19268=B$11)*('[Summary1.xls]A1
> > 1-3'!$E$3:$E$19268=$A9)*('[Summary1.xls]A1
> > 1-3'!$G$3:$G$19268=$H3)*'[Summary1.xls]A1 1-3'!$S$3:$Y$19268)}
> > the result should read 24 anf it give 0
> >
> > please advise
> > regards
 | 
Pages: 1
Prev: Migrating to something else
Next: Cell Referencing