SUM only the number of cells specified by the Logical Argument

[HangMan]

I want to be able to SUM specified cells in a column based on the Logical_Test of an IF formula:

Please Login or Register  to view this content.

The above Logical_Test resolves to '3', so if the logical test is true, I only want to SUM the first three cells, i.e. C7, C20 and C33, then when the logical_test resolves to 4, it will SUM C7, C20 C33 and C46 and so on.

Is there any way to achieve this or can it be achieved with a different formula?

Many thanks

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

[HangMan]

oeldere,

Sample attached...

Results wise, using the sample attached to 'this' post, with the formula

Please Login or Register  to view this content.

I would expect the result for column B to be 2,391, i.e. the SUM of B2, B15 and B28 and the result for column C to be 298 because the Logical_Test resolves to three, so it should only SUM the first three values in the list,then when the Logical Test resolves to 4, it will SUM the first 4 values ion the list and so on.

Many thanks

[oeldere]

With a pivot table.

[HangMan]

I realise I could easily show this using a pivot table, but I have a very specific need to show these as totals at the bottom of my sheet, so a pivot won't work, hence the formulaic approach.

[oeldere]

Please Login or Register  to view this content.

show it manualy in your file.

[HangMan]

This needs to autoupdate each month which is the whole purpose of trying to do this with a formula, no user interaction required.

[oeldere]

This can be done with a pivot table (and I guess will take less actions to update, regarding a formula).

But, you don't show what the result is, as requested.

[HangMan]

The result is shown in the attachment and detailed in my post at 9.10am. I realise this can be easily achieved using a pivot table however a pivot table is not what I'm looking for here, this needs to be formulaic I'm afraid.

Many thanks

[oeldere]

Please Login or Register  to view this content.

I don't see any data on the bottom of the sheet (in #3) !!!!!

[HangMan]

That is the point, this is where I'm needing a formula to create the totals based on the criteria in the fourmula, so if this element:

Please Login or Register  to view this content.

= 3, which it currently does then the formula should only sum the first three cells specified, i.e. B2, B15 and B28, if it = 4 then it sums B2, B15, B28 and B41 and so on. I'm thinking there needs to be an array of something, I just don't know how to do tis using a formula.

Please Login or Register  to view this content.

Does that help?

[oeldere]

Please also add manualy the expected result in your file.

One last shot:

as request in 2, 6, 8 and 10

[HangMan]

Hi oeklere,

The expected result is in 2nd the attachment posted at 9.10am as mentioned above...

[oeldere]

I quit, maybe another forummember is willling to help you.

[HangMan]

I'm sorry you feel that way, I posted a question asking for help with a formula, you asked for the expected answer, which is both in my posts and in the attached file and all you've done is say use a pivot table. If I'd wanted to use a pivot table and couldn't do it I'd have posted a question about how to create a pivot table.

I don't understand why this is a problem, it was a simple request and I provided all the information you asked for, but thanks for you time anyway.

[nigelbloomy]

OK. I was able to do it with a helper column. I hope you can use a helper column. I put your logical statement in cell E1. Column D is the helper that checks to see how many should be added up. F1 has the result you want which will change any time the logical statement does.
I know that is not as fun as having it all in one formula, but is that something you can work with?

[HangMan]

Hi nigel,

I very much appreciate your help... A helper column is likely to be an issue owing to the nature of the workbook but if this is the only way, I will have to find a workaround. I had wondered if this could be done using an array, so the array becomes the cells listed in the formula and somehow the formula then sums just the number of cells equal to the value of the logical test if that makes sense.

Thanks for this for now though...

[nigelbloomy]

I think this will work better for you. This formula is in G1:

Please Login or Register  to view this content.

E1 still has your logical formula. You can put that part back into this formula if you want to, in place of the E1 references. It just makes it hard to read and understand what is going on.

[HangMan]

That did the trick, perfect... I've put E1 back into the formula, it just keeps everything really neat. I take on board what you are saying about readability but that's not an issue in this case...

Many thanks for your help, very much appreciated.

[nigelbloomy]

This would also work. It just sums every 13th row starting with the 2nd row. This way you don't have to worry if the word Month is in column A. E1 still has the logic formula.

Please Login or Register  to view this content.

[HangMan]

Um, now why didn't I think of that, that is a far more sensible and practical solution and like you say avoids the exclusion of the word Month, which is a good thing...

Many thanks once again... greatly appreciated.