SUMIF & INDIRECT Help

[chelseagardens]

So I am attempting to sum data across multiple sheets. The idea is that if the customers name appears in column A on any of the sheets, then it will return a sum of the the numbers in column C. In effort to achieve this I entered the following into a cell:

=SUMIF(INDIRECT("'"&A331:A340&"'!A312:A450"),A2,INDIRECT("'"&A331:A340&"'!C312:C450"))

This gets me: #value. Any suggestions?

[FDibbins]

Hi and welcome to the forum

summing across sheets based on a specific criteria can be tricky. Try a slightly different approach using a helper cell in the same place on each sheet Then, if you will have more than 1 entry for the criteria, use sumif() based on the name in the master sheet. If there will only be 1 entry, use vlookup

Then on the master sheet, use a simple =SUM(Sheet2:Sheet10!A10)

[chelseagardens]

Thank you so much for a prompt response! There will be multiple entries for each customer so sumif is definitely the way to go. But, I am not sure what a helper cell is. Can you enlighten me?

[NBVC]

Wrap a SUMPRODUCT around it.


=SUMPRODUCT(SUMIF(INDIRECT("'"&A331:A340&"'!A312:A450"),A2,INDIRECT("'"&A331:A340&"'!C312:C450")))

[chelseagardens]

Actually, I got it. Thanks again!

[chelseagardens]

Unfortunately, the sumproduct did not work. There has to be some issue with my indirect formulas because even when I type them into a cell alone, it shows #value. But nothing looks wrong with it so I just did what fdibbins suggested and created helper cells on each sheet for each customer and then on the master sheet I was able to sum the specific helper cells from each sheet. Not as pretty as I'd hoped, but it works perfectly and that's all I need. Thank you both for your suggestions!

[FDibbins]

Thanks NBVC

[oeldere]

Another approach.

Put all data on one sheet (e.g. with VBA).

After that a pivot table can do the analize of the data.