SUMIF MULTIPLE SHEETS

[MikeMN]

I have used the sumif formula many times ofer the years.

However, I have always only refrences one sheet.

I am now tying to refrence several sheets.

But I am getting a #value error message.

Any thoughts if this can be done.

Thank you

Mike

 

[Aladin Akyurek]

Option 1 worked like a champ and was EXACTLY what I needed!! Thanks a million!!!

You are welcome.

 

[Aladin Akyurek]

Two options...

1]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion.

Define SheetList, asked for in Option 1, using Insert | Name | Define or Formulas | Name Manager as referring to:

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Note 1. The formula in (1) will work immediately after completing the foregoing definition.

Note 2. GET.WORKBOOK is a Microsoft Excel 4.0 macro function.

If needed, you can list the names of the sheets in a convenient range, say from A2 on, as follows:

Either...

=IF(ROWS($A$2:A2)<=COUNTA(SheetList),INDEX(SheetList,ROWS($A$2:A2)),"")

Or...

=IFERROR(INDEX(SheetList,ROWS($A$2:A2)),"")

Using formulas a specific subset can be extracted from the foregoing listing.

See for more

List all Worksheet Names Using Formula

XLM 4.0 MACRO | Ashish Mathur's Blog

 

[Antrew80]

Hi ,
as you can see in the image i have an excel with all the months of the year
what i want to do is have a 13teen sheet name Details and have a total of my expenses and incoming separately.
e.g. how much i spend for telephone and how much i get for salary,
i tried both ways descried here but with no luck..
can anybody help on this?

thanks in advance

Antreas

 

[Aladin Akyurek]

@Antrew80

What did you exactly try? Are you wanting to do a conditional sum across the month sheets, e.g., Telephone across January, February, etc?

 

[Antrew80]

i tried both variables below

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

yes i want the sum of telephone bills from January to December

 

[Aladin Akyurek]

i tried both variables below

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))

2] Using the morefunc.xll add-in...

=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))

yes i want the sum of telephone bills from January to December

You exhibit shows "details" in C, not in A.

Try the following on Details. Enter the month sheet names in column A from A2 downwards, that is, A2:A13. If you can, name A2:A13 as SheetList using the Name Box option.

Let C2 house Telephone.

D2, just enter:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C18"),C2,INDIRECT("'"&SheetList&"'!E2:E18")))

If you don't know how to name a range, invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$13&"'!C2:C18"),C2,INDIRECT("'"&$A$2:$A$13&"'!E2:E18")))

Note that this formula looks at C2:C18 (Details) for Telephone and sums E2:E18 (Debit) on each data sheet that you have.

 

[TIMB81]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C18"),C2,INDIRECT("'"&SheetList&"'!E2:E18")))

If you don't know how to name a range, invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$13&"'!C2:C18"),C2,INDIRECT("'"&$A$2:$A$13&"'!E2:E18")))

Hello,

I am trying to perform a similar function where I want to sum across all sheets for a part # (different # in each cell), where the totals are not in the same column on each sheet. There are 30 total sheets I want to sum, with a table in each totaling the number of parts per sheet.

I've tried the SheetList method and that hasn't worked for me yet, and the problem is compounded by my "TOTAL" column not being in the same place on each sheet. Any help on the matter would be greatly appreciated!!

Sample of components list for one part #:
[TABLE="width: 479"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]2CSH34029-6001[/TD]
[TD]Column1[/TD]
[TD]JOB #[/TD]
[TD]JOB #2056
[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]PART #[/TD]
[TD]DESCRIPTION[/TD]
[TD]QTY[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2CSH34029-0013[/TD]
[TD]FRAME - FS 402, UPPER RHS[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LMT02932[/TD]
[TD]AL PLATE[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EN1182ND6[/TD]
[TD]NUT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]JSFF17D24[/TD]
[TD]COUPLING[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]JSFM20-60[/TD]
[TD]STANDOFF[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]LMT00064[/TD]
[TD]FUEL TANK SEALANT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MS20470AD5-6[/TD]
[TD]RIVET[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]VS8008NN6-2-11[/TD]
[TD]PIN[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]



Sample of main tab: (column 1 is where I want the totals from all sheets to generate)
[TABLE="width: 505"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PART #[/TD]
[TD]DESCRIPTION[/TD]
[TD]Column1
[/TD]
[/TR]
[TR]
[TD]2CBH31621-0001[/TD]
[TD]CLIP ASSY, COAX[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2CBH31622-0001[/TD]
[TD]CLIP ASSY, COAX, FUEL FLOOR, FWD[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2CBH31622-2001[/TD]
[TD]CLIP, COAX, FUEL FLOOR, FWD[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2CBH31623-0001[/TD]
[TD]CLIP ASSY, COAX, FUEL FLOOR, AFT[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2CBH31623-2001[/TD]
[TD]CLIP, COAX, FUEL FLOOR, AFT[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2CBH32008-0005
[/TD]
[TD]CLIP ASSY[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[TIMB81]

=SUMIF(INDIRECT("'"&SHEETLIST&"'!A2:A415"),A187,INDIRECT("'"&SHEETLIST&"'!Table5[TOTAL]"))

This formula works, I just discovered. I've tested it on one sheet and it has returned the correct value from that one sheet. The next step is to get it to sum across all other sheets and tables. Would I need to list each table name or is there a way to reference them all like at once?

 

[Aladin Akyurek]

=SUMIF(INDIRECT("'"&SHEETLIST&"'!A2:A415"),A187,INDIRECT("'"&SHEETLIST&"'!Table5[TOTAL]"))

This formula works, I just discovered. I've tested it on one sheet and it has returned the correct value from that one sheet. The next step is to get it to sum across all other sheets and tables. Would I need to list each table name or is there a way to reference them all like at once?

Not sure I follow...

SHEETLIST must refer to a range in which the relevant sheet names are located.
Table5[TOTAL] must be equal in size to A2:A415.

 

[TIMB81]

Not sure I follow...

SHEETLIST must refer to a range in which the relevant sheet names are located.
Table5[TOTAL] must be equal in size to A2:A415.

I understand both of your points, and SheetList may be irrelevant if I reference the tables instead of the sheets. I'm not saying that you're wrong, but Table5[TOTAL] is nowhere near the same size as A2:A415 and it does return the correct values for the part #'s. I am trying to sum across all the sheets (whether it be the sheets via SheetList or by the Tables, it doesn't really matter which)

Sorry if I'm not being clear enough. Thanks for your help so far!