On sheet one (“Estimate”), I have created a form with an Invoice Number at cell G13. Further down, there are a range of codes in cells C17:C53, which refer to departments, e.g. BAR, CAF, VEN. In cells G17:G53, the sub totals are calculated. For example:
401
CODE |DETAILS |QTY |UNIT £ |LINE TOTAL
BAR Bottles of red wine 10 15.00 150.00
BAR Bottles of white wine 10 15.00 150.00
CAF Trays of sandwiches 50 5.00 250.00
VEN Room hire 1 200.00 200.00
BAR Mineral water 5 3.00 15.00
EQU Projector hire 1 20.00 20.00
785.00
On sheet two, I want to manage my profit and loss. I therefore want to bring forward all the invoice data automatically, and enter this into a corresponding line that matches the invoice number and sums the individual code totals from the previous sheet, e.g.
INV |BAR |CAF |VEN |EQU |TOTAL
400 275 150 300 50 775
401 315 250 200 20 785
402 750 400 1000 80 2230
403
404
I have tried using the following formula:
=SUMIF(ESTIMATE!$C$17:$C$53,"BAR",IF(ESTIMATE!G13=401,ESTIMATE!$G$17:$G$53,0))
This works as long as the invoice is 401…. but when I change the Invoice Number in sheet one to 402, I get an error (#VALUE) on sheet two, when I was expecting a zero. I would also have to code each row on sheet two this way to match the relevant invoice number, which seems clumsy. I’m sure this is relatively straightforward to solve, but I am getting knotted up in the problem and need some help! Does anyone have any suggestions for a more elegant solution please? I have supplied an example of the data as an attachment.
Thank you
Terri H
Bookmarks