Formula Help

[graham1996]

I am at a loss for a formula. Here is a mock up of my worksheet

Key: PA= Previous Authorized
ATP=Amount This Period
P=Pending


Code____CO#______Authorized______Amount_______Completed
___________________Amount_____This Period____ Previous Period
-----------------------------------------------------------------
PA_______1_________50,000_______25,000___________10,000

PA_______2_________25,000________5,000___________15,000

ATP______3__________1,000______________________________

PA_______4__________2,500______________________________

ATP______5___________800_______________________________

What I am trying to do is calculate: a) the previous authorized changes and b) the current or this period authorized changes by using the key (PA, ATP & P) as the deciding factor of whether or not that amount is calculated into the total.


For example:

Change orders 1 & 2 should be calculated in the total for the Previous Authorized changes.


Change orders 3 & 5 should be calculated in the total for the this period authorized changes.


Change order 4 should not be in the total for either.


Now let's say, next month we are billing: well change orders 3 & 5 should now be apart of the total for the Previous Authorized changes. And let's say that change order #4 is now ATP, so it should be calculated in the total for the this period authorized changes. And now we have change orders 6-8 that need to be sorted depending on their key (or code).



I appreciate any help you can give me.

 

[Juan Pablo González]

I think using a Pivot Table is your easieast answer. I would put for example "Code" as a row field, and "Authorized Amount" as a Data field.

You could also use SUMIF, something like

=SUMIF(A2:A100, "PA", C2:C100)

to sum all "PA" charges.

 

[graham1996]

Thank you Juan...the SUMIF worked great!