Default Summing values based on criteria

Hi guys,

I have a question which involves summing data values from a reference values based on a numerical criteria.

I know this sounds confusing (i might have phrased it poorly), so I'll try to illustrate this problem in the table below:

cosmic question.png

I have a list of input data in Column A, which is in an increasing order. It is a repetition of data (e.g. 0,1,2,3,0,1,2,0,1,2,3,4,5) where it will start from 0 for each repetition

I have a list of reference values (Column D11 to D14) from computations are to be made.

Expected result (Column B):

For the data in Column A, for the first 0 value (in A2), it is to use the first reference value (i.e. D11). It then sums up the cells respectively (see D2 to D4), until it reaches the next 0 value (in cell A10), where it will then use the next reference value (i.e. D12) for summing. This continues to the end of the data in Column A.

I am having a hard time to do this manually, as I am unsure what Excel formulas to use.

Could I have your advice please?

Thank you in advance.

Regards
Ben

Hi,

Formula on B2 :

Formula:

Please Login or Register  to view this content.