Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Jululian
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Jululian | Excel Discussion (Misc queries) | |||
Jululian Excel | Excel Discussion (Misc queries) | |||
Jululian Excel | Excel Discussion (Misc queries) | |||
Jululian Excel | Excel Discussion (Misc queries) | |||
Excel Jululian | Excel Discussion (Misc queries) |