anyway to make this code smaller?

Question

I have created a formula which index matches from different work sheets and then adds them together.

Is there anyway possible to make the coding smaller or better? I have tried and failed, but getting no results :(

.

I have the following code;

=IFERROR(INDEX('Report 1'!BC:BC,MATCH(B13,'Report 1'!$A:$A,0)),"0")
+IFERROR(INDEX('Report 2'!BC:BC,MATCH(B13,'Report 2'!$A:$A,0)),"0")
+IFERROR(INDEX('Report 3'!BC:BC,MATCH(B13,'Report 3'!$A:$A,0)),"0")
+IFERROR(INDEX('Report 4'!BC:BC,MATCH(B13,'Report 4'!$A:$A,0)),"0")
+IFERROR(INDEX('Report 5'!BC:BC,MATCH(B13,'Report 5'!$A:$A,0)),"0")

Answer 1

To get results from your formula remove the " from around the 0 so the formula is not trying to add text strings.

but we can shorten the formula with a 3D SUMIF

With the names of the desired sheets in a list and references them with INDIRECT in a SUMIF:

=SUMPRODUCT(SUMIF(INDIRECT("'" & D1:D5 & "'!A:A"),B13,INDIRECT("'" & D1:D5 & "'!BC:BC")))

Answer 2

Is there anyway possible to make the coding smaller or better?

Not really:(

Although you can use some workarounds:

• named range: you can define names for your ranges and refer to them
• support cell: use another cell to store the result of your MATCH function, so don't need to run it multiple times
• UDF: use a macro to create user defined function where you can write nicer code than your current formula