Solved: Add from Month of April Onward

[Eric58132]

Hi All,

I'm having some trouble with this one, a "higher up" has asked me to make what is already an automated file (created by me) include Fiscal Year totals. What I'm having an issue with is that whenever a new month of data comes in, my "April"(beginning of our fiscal year) column moves to a different column, thus I can't hard code any sort of sums or averages. I need a formula that scans the range of Month titles for April, and then sums up each row in the data set from april on. I've enclosed an example of what I mean. I REALLY hope someone can help me out with this!

Thank you in advance, and I'll be here if there are any questions.

[p45cal]

Three solutions in the attached. Yellow cells with sumproduct formulae are easiest. I can't help thinking I've gone round the houses on this one. The other two formulae require array-entering or you'll get #Value errors.

[Bob Phillips]

Try

=SUMIF(A4:H4,">="&MAX(INDEX((MONTH(A4:H4)=4)*(A4:H4),)),A5:H5)

[Eric58132]

thank you p45 and XLD. These seem to work perfectly for what I need. One last question: are any of these formulas easy to modify into an "average"? I looked around online and don't see the existence of any sort of "averageif" to compliment the "sumif". I imagine that all I need is some sort of count from april and the remaining columns to the right, which I can then divide by the original formula provided.

Does this seem right?



**Edit** I ended up tinkering around with a portion of the second formula in P45's file and got it to work I think. Will test further before saying that the problem is solved, but thanks guys!

[Bob Phillips]

You could use this array formukla

=AVERAGE(IF(A4:H4>=MAX(INDEX((MONTH(A4:H4)=4)*(A4:H4),)),A5:H5))

[Eric58132]

that one looks even easier, thank you for the assistance.

I love this forum, best place for access and excel help by far.

[Bob Phillips]

I love this forum, best place for access and excel help by far.

Can't argue with that