Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF
Can sumif work across multiple pages without naming each individual page e.g.
=SUMIF('P0195'!$A:$A,'Sum Parent'!$A19,'P0195'!B:B)+SUMIF('P0196'!$A:$A,'Sum Parent'!$A19,'P0196'!B:B)+SUMIF('P0197'!$A:$A,'Sum Parent'!$A19,'P0197'!B:B)+SUMIF('P0198'!$A:$A,'Sum Parent'!$A19,'P0198'!B:B)+SUMIF('P0199'!$A:$A,'Sum Parent'!$A19,'P0199'!B:B)+SUMIF('P0200'!$A:$A,'Sum Parent'!$A19,'P0200'!B:B)+SUMIF('P0201'!$A:$A,'Sum Parent'!$A19,'P0201'!B:B)+SUMIF('P0202'!$A:$A,'Sum Parent'!$A19,'P0202'!B:B)+SUMIF('P0203'!$A:$A,'Sum Parent'!$A19,'P0203'!B:B)+SUMIF('P0204'!$A:$A,'Sum Parent'!$A19,'P0204'!B:B)+SUMIF('P0205'!$A:$A,'Sum Parent'!$A19,'P0205'!B:B)+SUMIF('P0206'!$A:$A,'Sum Parent'!$A19,'P0206'!B:B)+SUMIF('P0207'!$A:$A,'Sum Parent'!$A19,'P0207'!B:B)+SUMIF('P0208'!$A:$A,'Sum Parent'!$A19,'P0208'!B:B)+SUMIF('P0209'!$A:$A,'Sum Parent'!$A19,'P0209'!B:B) |
#2
|
|||
|
|||
One way...
=SUMPRODUCT(SUMIF(INDIRECT("'P"&TEXT(ROW(INDIRECT( "195:209")),"0000")&"'! A:A"),'Sum Parent'!$A19,INDIRECT("'P"&TEXT(ROW(INDIRECT("195: 209")),"0000")&"'!B:B") )) OR =SUMPRODUCT(SUMIF(INDIRECT("'P"&TEXT(ROW(INDIRECT( E1&":"&F1)),"0000")&"'! A:A"),'Sum Parent'!$A19,INDIRECT("'P"&TEXT(ROW(INDIRECT(E1&": "&F1)),"0000")&"'!B:B") )) ....where E1 contains your first sheet name, such as 195, and F1 contains your second, such as 209. Hope this helps! In article , Kip wrote: Can sumif work across multiple pages without naming each individual page e.g. =SUMIF('P0195'!$A:$A,'Sum Parent'!$A19,'P0195'!B:B)+SUMIF('P0196'!$A:$A,'Sum Parent'!$A19,'P0196'!B:B)+SUMIF('P0197'!$A:$A,'Sum Parent'!$A19,'P0197'!B:B)+SUMIF('P0198'!$A:$A,'Sum Parent'!$A19,'P0198'!B:B)+SUMIF('P0199'!$A:$A,'Sum Parent'!$A19,'P0199'!B:B)+SUMIF('P0200'!$A:$A,'Sum Parent'!$A19,'P0200'!B:B)+SUMIF('P0201'!$A:$A,'Sum Parent'!$A19,'P0201'!B:B)+SUMIF('P0202'!$A:$A,'Sum Parent'!$A19,'P0202'!B:B)+SUMIF('P0203'!$A:$A,'Sum Parent'!$A19,'P0203'!B:B)+SUMIF('P0204'!$A:$A,'Sum Parent'!$A19,'P0204'!B:B)+SUMIF('P0205'!$A:$A,'Sum Parent'!$A19,'P0205'!B:B)+SUMIF('P0206'!$A:$A,'Sum Parent'!$A19,'P0206'!B:B)+SUMIF('P0207'!$A:$A,'Sum Parent'!$A19,'P0207'!B:B)+SUMIF('P0208'!$A:$A,'Sum Parent'!$A19,'P0208'!B:B)+SUMIF('P0209'!$A:$A,'Sum Parent'!$A19,'P0209'!B:B) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions |