Formula for using value in one column if value in another is zero

[ArchieDiamond]

Hi all, I hope I'm not insulting anyone's intelligence by even asking this question I suspect the solution is screamingly obvious but I can't work it out so would be so grateful for any help you can provide.

I am working on a budget and different items are coded differently, for example all the security costs are coded "6516'. In column F is the amount of money that I have estimated the cost will be and issued a purchase order for. In column G is the actual amount that was invoiced. I'm now doing a column of estimated final costs that shows the actual costs incurred where possible but, when invoices haven't come in, shows the amount in the purchase order column (ie column G).

So I need the formula to calculate "where an item is coded 6516 (for example) take the amount in column F, unless that amount is zero, in which case take the amount in column G".

I hope I explained that clearly - apologies if not!!

Again - thanks so much for any help people can give me.

[Tony Valko]

I think we'll need to see some sample data.

Can you post a SMALL sample file and show us what results you expect?

20 rows worth of data is plenty.

[ArchieDiamond]

Sure thing - I will try and post below..

As you'll see, the PO (purchase order) amount of everything with the same code number in the actuals section has been added up in the running estimates column up the top.

What I'm wanting to calculate is the "Estimated Final Costs" amounts up the top. What I'd like the formula to do is sum the "actual" amount of everything with the same code number UNLESS the "actual" amount is zero (which means I haven't yet received the invoice for it) in which case I'd like it to use the PO amount (as this is the best estimate I have).

Hope this makes sense.

[ArchieDiamond]

Please Login or Register  to view this content.

[forilohud]

I'm not sure about what you need, but here is a formula that sums:

ONLY when the red column is equal to the value in Column B
sums purple column if (purple column <>0)
adds green column if (purple column =0)

2po0Voi.jpg

Is an array Formula, so it should be entered with control+shift+enter

[FDibbins]

Hi, welcome to the forum

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ArchieDiamond]

OK, I have tried to attach here...

[FDibbins]

Try this...
=SUMIF(B:B,$B2,G:G)+SUMIFS(F:F,B:B,$B2,G:G,0)

btw, you can change the SUMIF formula to this...
=SUMIF(B:B,$B2,F:F)

[ArchieDiamond]

Thank you so much for this. If I need the values for each code to go up in their corresponding cell under the "Estimated Final Costs" are you able to tell me how I do that? Thank you so much!

[FDibbins]

My suggested formula would go in J2 of your sample
copied down

[ArchieDiamond]

Yes, that's perfect, thank you so much!!!! You have saved me hours (days?) of work, I really really appreciate your help :-)

[FDibbins]

Happy to help

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[ArchieDiamond]

Will do, thanks so much