Problem with the getting all locations sales using iferror, index, match

[brandedadnan]

Dear All,

I have an excel sheet where I have to collect the data for the all locations sales at one point and arriving at the consumption of certain materials.

Now I am using iferror, index match to get the whole location data.

However I am facing a problem, when i use sum to add all the figures in one cell.

Any Help is highly appreciated.

Pls see the attached file.

Rgds
AT.

[benishiryo]

hi AT. change all the spaces for the IFERROR to zero.

Please Login or Register  to view this content.

you also don't need:
1) the brackets surrounding the formula
2) the sheet reference 'Agg Sales'! if it's referring to the sheet the formula is in

so:

Please Login or Register  to view this content.

Edit: if you get rid of the extra space in the name of the "Location B " sheet, you could also use:
=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:F1&"'!A:A"),A2,INDIRECT("'"&C1:F1&"'!B:B")))

[azumi]

=SUM(('Location A'!A2:A4=A2)*('Location A'!B2:B4))+SUM(('Location C'!A2:A4=A2)*('Location C'!B2:B4))+SUM(('Location D'!A2:A4=A2)*('Location D'!B2:B4))

Try this Array Formula

Azumi

[ramananhrm]

Please try this file.

[brandedadnan]

@ benishiryo, Thanks, it worked perfectly Fine. it solved mu
@Azumi & ramananhrm, Thanks, I will work on the solutions that you have provided to me.
Its completely new to me. so will have to work out your methods.

Thanks all again. This problem was bugging me for some time now.