Sum Products/Sum If error

[JohnFex]

Hello,

Please see the attached sheet and the formulas in the 'ENE' worksheet. I am trying get the figures to accumulate in Asistencia through the year but the formula isn't working.

Any help much appreciated.

Thanks,


John

[Richard Buttrey]

Hi,

If your data was going to be consistent on each monthly sheet then a simple =SUM(Start:END!A1) formula would sum across each sheet between two blank anchor sheets 'Start' and 'End'

However you have more names on your Asistencia sheet than you have on the first January sheet and I'm assuming that each sheet may therefore have a different layout in terms of the rows.

All that said, you would be much better advised to keep all your data on a single sheet. All you need to do is use the current columns and add an additional column for the month. When you have this simple two dimensional database you'll then be able to use a Pivot Table to analyse and summarise your data in any way you want.

You are just complicating things at the moment and any solution using formulae will be no doubt require quite complex array formulae. A macro would be another solution but in all honesty I'd go for the single database sheet.

[JohnFex]

I agree that a Pivot Table would be the simpler way here. However the months need to be separated and the client has requested this format.

More importantly though, I would be interested to see where I went wrong...

Thanks,


John

[daddylonglegs]

Hello John,

With the formula you tried all the sheets listed in K2:K9 must exist otherwise you get an error - if you create all 8 sheets your formula will work, or restrict the sheet range to K2:K3 for the two sheets that currently exist.....alternatively if you want the formula to reference K2:K9 when some of those sheets may not yet exist you can add an IFERROR function, i.e. like this

=SUM(IFERROR(SUMIFS(INDIRECT("'"&$K$2:$K$9&"'!$D$5:$D$50"),INDIRECT("'"&$K$2:$K$9&"'!$B$5:$B$50"),$B5,INDIRECT("'"&$K$2:$K$9&"'!$C$5:$C$50"),$C5),0))

that now needs "array entry", i.e. confirm with CTRL+SHIFT+ENTER

[Richard Buttrey]

Hi,

Because you don't yet have sheets for March - August. i.e. your K2:K9 references. If you add those sheets or change to K2:K3 then it will work.

However I still think this is unnecessarily complicated. One thing I've learned over the many years I've dealt with this stuff is that clients are totally unaware of how powerful Excel can be if they just rethink their mindset.

Many people mix up the two elements of data capture and final reporting. The two require quite different treatments.
A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In this case, and if the client still exists on this layout I'd turn this on its head and urge you to capture the data in the table I described earlier. Then in order to produce the individual sheets that the client seems to want, just have a simple looping macro that filters data from the data sheet to the individual monthly sheets. Or better still if the client can adapt to a single monthly sheet where you select the month from a drop down and that particular month is filtered out.

[JohnFex]

Thank you to you both - this is definitely my favourite forum!

The explanation and addition of IFERROR worked beautifully and I now have all my sheets up and running.

I do enjoy a good pivot table as much as the next man, but I currently use them for very basic tables and calculations and would be interested to learn how to get them to function across multiple worksheets if you could me in the direction of any info that relates to what I'm doing.

Thanks again!


John

[AZ-XL]

Hi
=IFERROR(INDEX(ENE!$D$5:$I$11,MATCH(1,(ASISTENCIA!$A10=ENE!$A$5:$A$11)*(ASISTENCIA!$B10=ENE!$B$5:$B$11)*(ASISTENCIA!$C10=ENE!$C$5:$C$11),0),MATCH(ASISTENCIA!E$4,ENE!$D$4:$I$4,0)),"") CSE