make a sumif formula dynamic

**amartino44** · 03-05-2013, 07:15 PM

Hello. Any suggestion on how I can make the following formula dynamic? In E4 and beyond I have a formula that will change the cell from "estimate" to "actual" when I insert a tab with actuals information. Currently in the C column, I have the formula below running through January, because that is what I have actuals through. I would like to make this dynamic so that when actuals comes in, the formula below calculates for two months instead of just one. Thank you.

=IFERROR(SUM($E16)/(SUMIF($B$15:$B$66,$B16,$E$15:E$66)),0%)

**Tank997** · 03-05-2013, 08:14 PM

All your formulas in E15 to DN 73 evaluate to #REF! errors which make it very hard to see what you're trying to do here... Can you fix those so we can follow he formulas?

**amartino44** · 03-05-2013, 08:27 PM

Just imagine that those ref errors are numbers. They are montly numbers that sum up to a total. They update based on whether the month is actual/estimate. Now, I would like to do the same for % in column C. Make sense?

**Tank997** · 03-05-2013, 08:40 PM

Sorry, I'm a numbers guy my imagination has a hard time testing things without real numbers to see what changes when formulas change. All 0's and errors make that very hard for me

**amartino44** · 03-07-2013, 05:53 PM

Ok. I've attached the file with updated numbers. There are only numbers for January because that month is acutals. The rest of the months update when actuals come in. Now, how can I get the %'s in column C to update (that is, the % will be based on all actual months rather than just the first one) Based on whether E4, E5, E6, says actuals. Thanks!!

**Tank997** · 03-07-2013, 06:55 PM

This will do what you asked but looking at your data again I don't think it's what you want. I think if you want to handle the whole 12 months, or more you want an array formula and they make my head spin... Good Luck...

Please Login or Register  to view this content.

**Tank997** · 03-08-2013, 11:19 AM

Okay so your question had me thinking of a way to do this, If I understand your sheet, you are looking for the % that one state is of a division's total for months with "Actual on row 4. If that's correct the array formula I added in column works. I removed most of your other formulas from the sheet because the calculations were extremely slow on my computer. You could probably change some of the other formulas as well and speed up the calculations dramatically.

Hope This Helps.
Tom

If you edit the formula you need to press CSE ( Ctrl+Shift+Enter ) for the array formula to work.