Conditional summing across multiple sheets

[Utterly Bamboozled]

Hi,

I am trying to sum the same cell across multiple sheets, but want to incorporate a summing criteria. Ordinarily I would use '1:3'!C23 (for example) to sum the same cell (C23) across sheets 1 to 3.

However, say C23 on sheet 2 doesn't fit my criteria (such as cell C1 on all sheets being the name of a client), yet C23 on sheets 1 and 3 do - is it possible to use this type of formula and incorporate a criteria such that the result would be sum('1'!C23,'3'!C23)?

Thanks in advance,

UB

 

[Aladin Akyurek]

Hi,

I am trying to sum the same cell across multiple sheets, but want to incorporate a summing criteria. Ordinarily I would use '1:3'!C23 (for example) to sum the same cell (C23) across sheets 1 to 3.

However, say C23 on sheet 2 doesn't fit my criteria (such as cell C1 on all sheets being the name of a client), yet C23 on sheets 1 and 3 do - is it possible to use this type of formula and incorporate a criteria such that the result would be sum('1'!C23,'3'!C23)?

Thanks in advance,

UB

Try...

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!C1"),"IBM",INDIRECT("`"&SheetList"`!C3")))

where SheetList is a range housing the relevant sheet names.

 

[Utterly Bamboozled]

Aladin, thankyou for you response. I apologise for taking so long to get back to you. Your advice worked well!

Regards,

UB

 

[Aladin Akyurek]

Aladin, thankyou for you response. I apologise for taking so long to get back to you. Your advice worked well!

Regards,

UB

You are welcome. Thanks for providing feedback.

 

[skyshow1]

This works great; however, I have a table that I need to use the formula in. When I drag the formula down, the cell references do not increment. E.g., for:

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!$C$1"),"IBM",INDIRECT("`"&SheetList"`!C3")))

...even though !C3 is a relative reference, it will stay !C3 in all cells when the forumula is copied down or across. Is there a way to have Excel update this reference as relative?

 

[Aladin Akyurek]

This works great; however, I have a table that I need to use the formula in. When I drag the formula down, the cell references do not increment. E.g., for:

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!$C$1"),"IBM",INDIRECT("`"&SheetList"`!C3")))

...even though !C3 is a relative reference, it will stay !C3 in all cells when the forumula is copied down or across. Is there a way to have Excel update this reference as relative?

Try...

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!$C$1"),"IBM",
     INDIRECT("`"&SheetList"`!"&CELL("address",C3))))

 

[skyshow1]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!$C$1"),"IBM",
     INDIRECT("`"&SheetList"`!"&CELL("address",C3))))

Excellent suggestion - using the CELL function worked! One change though... I needed to add a "&" after SheetList in both locations where it is used in the formula (I noticed I had that already in my working spreadsheet). Here is the final working formula:

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList&"`!$C$1"),"IBM", 
     INDIRECT("`"&SheetList&"`!"&CELL("address",C3))))

Thanks Aladin! You are super helpful and made my day! I was struggling with this for a while.

 

[Aladin Akyurek]

Excellent suggestion - using the CELL function worked! One change though... I needed to add a "&" after SheetList in both locations where it is used in the formula (I noticed I had that already in my working spreadsheet). Here is the final working formula:

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList&"`!$C$1"),"IBM", 
     INDIRECT("`"&SheetList&"`!"&CELL("address",C3))))

Thanks Aladin! You are super helpful and made my day! I was struggling with this for a while.

You are welcome. Yes, another & is needed there.

 

[Excel74]

This works great; however, I have a table that I need to use the formula in. When I drag the formula down, the cell references do not increment. E.g., for:

=SUMPRODUCT(SUMIF(INDIRECT("`"&SheetList"`!$C$1"),"IBM",INDIRECT("`"&SheetList"`!C3")))

...even though !C3 is a relative reference, it will stay !C3 in all cells when the forumula is copied down or across. Is there a way to have Excel update this reference as relative?

Hi,I am new in these terms (" ' " & ) I am having the same problem that I need to add different tabs with one condition, but I cant figure what goes inside those symbols or instead of those.

Would you please explain a little more in detail this

 

[Aladin Akyurek]

Hi,I am new in these terms (" ' " & ) I am having the same problem that I need to add different tabs with one condition, but I cant figure what goes inside those symbols or instead of those.

Would you please explain a little more in detail this

Would you care to describe what you need to do just using one sheet?