Formula for Identifying Actions against periods

[xBrito101]

I need a formula for sorting project spend activities between each period

On the master sheet, in column A I have a list of project activities, across the columns I have each month, (ie Jan = P1, Feb = P2 etc)


On the input sheet, column A is for entering the appropriate period (P1, P2 etc) and Column B for selecting the associated activity, A further column is for listing the amount of spend.


What I need is a formula that looks at the 'Input Sheet' reads the period in column A, the associated Activity in column B, and the amount. and places that figure, in the Master sheet, not only on the correct row of the same action, but in the appropriate period column.


Example Master sheet

Activity	P1	P2	P3	P4
Resource
Consultancy
Development
Training
Communications

Input sheet

Enter Period	Enter Activity	Enter Amount
P3	Training	£5000
P4	Development	£10,000

If the end user enters 'P3' in column A, 'Training' in Column B. and enters '£5000' in Column C, The calculation would enter on the Master Sheet, £5000 under column D, but on Row 5. (cell D5)
If the end user enters 'P4' in column A, 'Development' in Column B. and enters '£10000' in Column C, The calculation would enter on the Master Sheet, £10000 under column E, but on Row 4. (cell E4)


Resulting Master sheet

Activity	P1	P2	P3	P4
Resource
Consultancy
Development				10000
Training			5000
Communications

That's the first step, The second step if there were two training actions within P3 the formula would need to calculate, and enter the total amount in cell D5.

[63falcondude]

Hello and welcome to the forum.

I would format the data in the 'Input' sheet as a table and then use this formula in B2 of the 'Master' sheet:

=SUM(IF((Table1[Enter Period]=B$1)*(Table1[Enter Activity]=$A2),Table1[Enter Amount])) Ctrl Shift Enter

You can then drag the formula across and down.

[63falcondude]

On second thought, this will be more efficient (and does not require formatting as a table):

=SUMIFS(Input!$C:$C,Input!$A:$A,B$1,Input!$B:$B,$A2)