Sumifs across multiple worksheets

Hello,

I am trying to 'sumifs' across multiple worksheets, but am failing. Please see the attached sheet and the cell A5 on 'Assistencia' for what I am trying for but I want to calcualte a total of all the months onto the 'assistencia' sheet.

I have looked on the forum and I can see there are very elegant ways of doing this, but I just can't seem to replicate them. I would appreciate any help and also, if anyone has the time, it would be great if you could just breakdown a little what the different parts of the formula refer to as I would love to learn the technique.

Many thanks,


John

...am trying to 'sumifs' across multiple worksheets, but am failing. Please see the attached sheet and the cell A5 on 'Assistencia'

You mean,In D5??

Then try this.

=SUMPRODUCT((('ENE 2012'!B5:B10="JENNIFER")*('ENE 2012'!C5:C10="SOTO")*('ENE 2012'!D5:D10)))

Hope to helps you.

I'm not quite sure where you're going with this. I don't believe you can use SUMIFS or SUMPRODUCTS across sheets. You *can* use SUM.

The way to approach this is to "bookend" the sheets that you want to sum. For example, create Start and End worksheets (which you don't need to access and can hide). And then you would have the following sheets:

Start .. January .. February ........ November .. December .. End.

Then, if you want to sum all the cells at E5 on each sheet, instead of:

=SUM('ENE 2012'!E5)+'FEB 2012'!E5+'MAR 2012'!E5+'APR 2012'!E5+'MAYO 2012'!E5+'JUN 2012'!E5+'JUL 2012'!E5+'AGO 2012'!E5+'SEP 2012'!E5

you would have:

=SUM(Start:End!E5)

You can drag this across for: =SUM(Start:End!F5)
and: =SUM(Start:End!G5)


And don't use hard coded names such as:

=SUMPRODUCT((('ENE 2012'!B5:B10="JENNIFER")*('ENE 2012'!C5:C10="SOTO")*('ENE 2012'!D5:D10)))

Use:

=SUMPRODUCT((('ENE 2012'!$B$5:$B$10=$B5)*('ENE 2012'!$C$5:$C$10=$C5)*('ENE 2012'!$D$5:$D$10)))


Regards, TMS

Yes, you could use existing workesheet names but, if you delete either of them it will break the formula. And, if you add sheets after the last one, it will not include them in the SUM. That's your choice though.

If you use hard coded names, every formula is unique in that it can only relate to a specific person. If you use a cell reference, you can drag it down and it will automatically refer to the name on that row. It also means that there are no issues of formulae not working as you expect them to because you have spelt the name slightly differently. Again, your choice but you will make the spreadsheet very labour intensive. It's OK if you've only got a dozen or so rows, even up to 20,30, or 40. But, if you get into 100s and 1000s, it's going to get hard work. And, if someone edits the "visible" names in the workbook, for example, from Jane to Jayne, your formula will still work, it might just return 0.

Have a look here:

http://www.xl-central.com/sum-single...le-sheets.html

you can use SUMIFS for multiple criteria in that formula....

I've applied the formula that NBVC has referenced. See attached workbook.

Regards, TMS

You're welcome.

You're Welcome.