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
======
