VLOOKUP, Sorting, Calculate

[rldjda]

I have a project to create a "running" report that calculates various
formulas for each particular jobID, but the job ID's aren't entered yet ( to
be entered in the future in another worksheet within the same workbook), and
when they are, their are multiple jobID's to be summed up to create one jobID
with one value.
This is my scenario-

Expenses! (worksheet) has the following data
A1 = Date
B1 = jobID
C1 = totalexpense
note: one jobID may appear several times in B column depending on the number
of purchases made for that jobID

Profits! (worksheet within same workbook as above) has the following data
A1 = jobID
B1 = totalexpense
C1 = formulas I will enter later

How can I have Profits!A1 automatically sort through Expenses!A1:B100 to add
up the totals expenses for each respective jobID AS THE JOBID's ARE BEING
ENTERED IN THE FUTURE? For example, jobID100 apears 8 times with different
values each time. I need jobID100 to appear in Profits!A1 and the cumulative
total of the 8 times an expense was entered for jobID100 entered in
Profits!B1. Profits!B1 needs to be able to change in the event jobID100
appears again in the future.

Thanks in advance.

 

[Toppers]

ASSUMING expenses can be sorted by JOBID then in Profit:

A2: =Expenses!B2
A3: =OFFSET(Expenses!$B$2,COUNTIF(Expenses!$B$2:$B$5000,"<="& Profit!A2),0)
and copy down until 0 value as JobID


B2:
=SUMPRODUCT(--(Expenses!$B$2:$B$5000=Profit!$A2)*(Expenses!$C$2:$C$5000)) and
copy down

Change ranges of B & C to suit.

HTH

 

[Toppers]

Alternatively, look at using a pivot table.

ASSUMING expenses can be sorted by JOBID then in Profit:

A2: =Expenses!B2
A3: =OFFSET(Expenses!$B$2,COUNTIF(Expenses!$B$2:$B$5000,"<="& Profit!A2),0)
and copy down until 0 value as JobID


B2:
=SUMPRODUCT(--(Expenses!$B$2:$B$5000=Profit!$A2)*(Expenses!$C$2:$C$5000)) and
copy down

Change ranges of B & C to suit.

HTH

 

[driller]

hi rldjda
you have an interesting post for me...
1. Is the plan to create a JOBID will be in series (eg. 100,101,102) ?
2. Which will be in series for FUTURE entry on Expense! ? The DATE or the
JOBID.... OR BOTH?
3. In the Profit! "since you will prepare a formula in column C " which
maybe the PROFIT per JOBID", are you planning to sum the planned expense or
the Actual Expense which are entered on Expense! by the Dates<=Today() or

Today()....?

the word future entry for me seems you need the ACTUAL in reference to
TODAY()...

regards

ill the entry of JOBID and Expenses in Expense! will be in series