MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I need help with a formula......


Posted by Mark on January 11, 2002 7:34 PM

I've set up a workbook for invoices. I bill jobs separately, each job has it's own job number. I have a formula that assigns each invoice a sequence number (for instance, job number 123's invoices are 123-1, 123-2, 123-3....you get the picture). I also have a macro that sends the job number , sequence number & the invoice total to a ledger for all jobs/all invoices on another sheet.

My question, how can I get the sum of all invoice totals into a cell on another sheet for each job?? any ideas?? Thanks


Posted by Aladin Akyurek on January 13, 2002 4:59 PM

Mark --

Given the absence of the exact lay-out info, I have to guess.


If the lay-out looks like what follows:


{"Proj#","Seq#","Amount";
123,1,50;
123,2,60;
124,1,100;
124,2,124;
124,3,200}

which spans, say, A1:C6, then


in D2 enter: 123 [ a target Proj# ]

in E2 enter: =SUMIF(A:A,D2,C:C)

Copy down this if you have more target Proj#'s in D for which you want to have invoice totals.

If the lay-out looks like what follows:

{"Inv#","Amount";
"123-1",50;
"123-2",60;
"124-1",100;
"124-2",124;
"124-3",200}

which spans, say, A1:B6, then

in C2 enter: 123 [ a target project number ]

in D2 enter: =SUMPRODUCT(((LEFT($A$2:$A$6,SEARCH("-",$A$2:$A$6)-1)+0)=C2)*($B$2:$B$6))


Copy down this if you have more target project numbers in C for which you want to have invoice totals.

Note. The last formula cannot be fed with whole columns as range arguments. If interested, there is a workaround that allows feeding SUMPRODUCT whole columns as range args.

Aladin

======