summing ranges by criterias

[adygelber]

Hello,

I am really stuck in something and I have to find a solution very quick. This is why I will be very gratefull if someone could help me.

Basically I have to sum some cells following some criterias. To be more clear I attached the XLSM file so we can speak on the subject.

We have the following input data on the WorkSheet "Input":
- 4 offices (OFF1, OFF2, OFF3, OFF4) in the column D but the number is a variable in time. Every office has an acronym in the column E (OFF1=OF1, OFF2=OF2 and so on);
- 14 sections in the column G (SOF11, SOF12, SOF13, SOF14, SOF21, SOF22, SOF31, SOF32, SOF33, SOF34, SOF35, SOF41, SOF42, SOF43), but the number is also a variable. Every section also have an acronym in the column H (S11, S12, S13 and so on);
- in column I is the acronym of the office that the respective section belongs to (every section belongs to one of the four initial offices). Therefore an office can have a variable number of sections.

Ok, even if I wrote much, the data is quite simple.

Now, in the WorkSheet named SPUNTA, on the line 32 in our example there are some X and Y but instead of X and Y I have to make some sums, following the next pattern:

instead of the X from C32 I have to sum C31, E31, G31 and I31
instead of the Y from G32 I have to sum in a similar way D31, F31, H31, J31
instead of the X from K32 I have to sum K31 and M31
instead of the Y from M32 I have to sum L31 and N31
and so on...

As you can see every X has to be the sum of the input columns for the respective office and every Y has to be the sum of the output columns for the respective office. Every section has an Input and an Output column.

The problem is that the number of offices and sections vary in time and even if the whole file is generated relying on the same variables, I get stuck at this final step.
In the VBA script I delimited the script which has to be modified to make sums instead of X and Y with ------------------- HELP HELP HELP -------------------

OK, this is it. I hope someone will have the patience to read my messagge and help me with an advice.

Thank you in advance,
Adrian

[walruseggman]

Edit: This isn't really what you were asking for, my apologies. I missed the part about the variable number of offices. I'll try to look at that when I'm free.

Here you go, I did the first one for you, i think you can extropolate the others, they'll be very similar.

Please Login or Register  to view this content.

The above works assuming you have the X's and Y's in the last row (32 in this case). If they're going to be empty, then just add 1 to sum row , as it should see the formulas in the row above (row 31 in this case) as the last used row.

[adygelber]

Thank you for your quick reply!

Unfortunately, your solution doesn't work if the number of offices/sections change and these will always be variables.

For a better understanding, I attached a new version of the file in which I added a new section (S15) to the first office (OF1) but the sum remains C31 + E31 + G31 + I31 instead of adding the new section automatically. After adding a new section the sum should be: C31 + E31 + G31 + I31 + K31, therefore all the input totals for the respective office.

I hope I was enough clear and thank you again for helping me.