SUMIF anomaly - Help Needed

[Chris Mitchell]

I have 3 Columns, with headers and content as follows:

Col A = Month = Jan > Dec
Col B = Forecast for each month and a total
Col C = Sales for each month and a total

Col A & B are filled in at the beginning of the year and Col C starts out as
all "£0"s and is updated once a month when sales figures for the preceding
month become available.

I want to be able to calculate by means of a formula the total of Sales to
date (Col C) + Forecast for the rest of the year (Col B).

I have =C14+SUMIF(C2:C13,0,B2:B13) which works provided that each months
Sales is positive, but fails if and when a months Sales = £0.

Note. I need to start with these cells being set to £0 to satisfy other
formulas.

The problem doesn't show until a positive Sales value for any month
following the month when Sales = £0 is entered when the above formula adds
in the Sales = £0 month's Forecast, which unless it is £0, as it can be, has
been missed and should be ignored.

How can I modify my formula or create a new one to allow for this anomaly?

Note. I would prefer a modified formula to a VB solution, unless it was
fully explained in detail as I am not familiar with VB.

 

[Bob Phillips]

Try this

=C14+SUM(OFFSET(B1,MATCH(TEXT(TODAY(),"mmm"),A2:A13,0),0,12-MATCH(TEXT(TODAY(),"mmm"),A2:A13,0)+1))

 

[Chris Mitchell]

Thanks Bob Phillips.

Try this

=C14+SUM(OFFSET(B1,MATCH(TEXT(TODAY(),"mmm"),A2:A13,0),0,12-MATCH(TEXT(TODAY(),"mmm"),A2:A13,0)+1))

--

HTH

Bob