|
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) |
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) |
| All times are GMT +1. The time now is 08:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com