Hi
I am going insane trying to figure out my SUMIFS formula and I am looking for any help please! I have a job tracker which is an intergral part of keeping track of what jobs are costing us and the profit for each one, however I am trying to tally it up with our accounts on a monthly basis. So I have added in a date at the top so that at any date I choose, April, May or June, I can see how our job tracker should have looked at this point in time. The only part I am struggling with is the part where we need to work out our accrual, i.e. we haven't received an invoice at the month end relating to a job in that month (apologies if this sounds patronising), so we need to put in a figure so that our accounts for that month show an accurate picture.
My problem is that where we have received an invoice later in the year the below formula works, however, if we still have not received an invoice therefore rather than a date to lookup there is just a blank it returns zero rather than the expected figure. I need to work out how to put an OR statement to look up for invoices which have been received later on in the year but also for a blank - i.e. still no invoice received.
It currently looks like this:
=IF($C6="Invoice",SUMIFS(Costs!H:H,Costs!A:A,B6,Costs!K:K,">"&$B$2,Costs!B:B,"Invoice"),SUMIFS(Costs!I:I,Costs!A:A,B6,Costs!K:K,">"&$B$2,Costs!B:B,"Credit"))
Thank you, any help will be greatly appreciated as I am currently banging my head against a brick wall here!
Faye
Bookmarks