# Sum some values across columns based on availability of data in other columns

#### krapley

##### New Member
I'm having difficulty figuring out how to create an equation and would appreciate help creating the equation or direction to reference material. I'm using Excel 2010 and Windows 7. I'd like to sum the monthly "Forecast" values, but only if "Actual" values for the month are entered. I would like to create a formula to do this so I don't have to continually change the =C3+E3+G3+I3 formula each time an actual data point is entered into the spreadsheet as shown below. I've trying things with SUMIFS, SUMPRODUCT, HLOOKUP, but can't seem to put the right combination together or locate a similar issue in a previous thread. Any suggestions would be great! Thanks.

 2013 2013 Jan Jan Feb Feb Mar Mar Apr Apr Forecast Actual Forecast Actual Forecast Actual Forecast Actual Forecast Actual 500 900 110 210 120 220 130 230 140 240 360 660 110 210 120 220 130 230 140

<tbody>
</tbody><colgroup><col><col span="9"></colgroup>

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I'm having difficulty figuring out how to create an equation and would appreciate help creating the equation or direction to reference material. I'm using Excel 2010 and Windows 7. I'd like to sum the monthly "Forecast" values, but only if "Actual" values for the month are entered. I would like to create a formula to do this so I don't have to continually change the =C3+E3+G3+I3 formula each time an actual data point is entered into the spreadsheet as shown below. I've trying things with SUMIFS, SUMPRODUCT, HLOOKUP, but can't seem to put the right combination together or locate a similar issue in a previous thread. Any suggestions would be great! Thanks.

 2013 2013 Jan Jan Feb Feb Mar Mar Apr Apr Forecast Actual Forecast Actual Forecast Actual Forecast Actual Forecast Actual 500 900 110 210 120 220 130 230 140 240 360 660 110 210 120 220 130 230 140

<tbody>
</tbody>

Hello and welcome.

Excel 2010
ABCDEFGHIJ
120132013JanJanFebFebMarMarAprApr
2ForecastActualForecastActualForecastActualForecastActualForecastActual
3500900110210120220130230140240
4360660110210120220130230140
5
6
73120All actual
8Actual
9Feb440
Sheet4
Cell Formulas
RangeFormula
A7{=SUM(IF(\$A\$2:\$J\$2=A8,\$A\$3:\$J\$4))}
B9{=SUM(IF(\$C\$2:\$J\$2=A8,IF(\$C\$1:\$J\$1=A9,\$C\$3:\$J\$4)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Are you manually entering all of this data? If so, I would suggest formatting it a little differently.

With the alternating forecast/actual, it's going to be very difficult to use any ranges - but I'm trying.

Edit: Robert Mika is not human

Last edited:
Hello Robert. Thank you for the welcome and your response! I've looked at your array formula and it doesn't seem to result in the number I would expect. I may not have been clear enough in my description. I'll try again. The results I would expect are in cells A3 & A4. The result of 500 in A3 would be generated by summing C3 (110) + E3 (120) + G3 (130) + I3 (140) after determining that D3, F3, H3, and J3 had values. In other words, only sum "Forecasts" if "Actuals" are entered. The result of 360 in A4 would be generated by summing C4 (110) + E4 (120) + G4 (130) but not include I3 (140) after determining that D4, F4, and H4 had values but J3 did not. Hope that provides more clarity and you'll take another crack at it. Thanks again

Hi Piktro,
Thanks for the response. Yes, the forecast and actual data is entered manually. Folks using these results want to see them side by side, so that's why they are arranged that way, but I'd like to hear any suggestions you may have.

Hi Piktro,
Thanks for the response. Yes, the forecast and actual data is entered manually. Folks using these results want to see them side by side, so that's why they are arranged that way, but I'd like to hear any suggestions you may have.

My recommendation (much easier to compare side by sides)

 [A1] [B1]Forecast [C1]Actual Jan 110 210 Feb 120 220 Mar 130 230 Apr 140 May Jun Jul Aug Sep Oct Nov Dec Totals

<tbody>
</tbody>

Then in B14

Code:
``````[TABLE="width: 214"]
<tbody>[TR]
[TD]=IF(COUNT(B2:B13)=COUNT(C2:C13),SUM(B2:B13),"NoActual")
[/TD]
[/TR]
</tbody>[/TABLE]``````
This will work assuming you're never more than 1 month behind.
ie: Once April is over, you will have data in April's actual and it will include that forecast.

Piktro,
Thanks for the suggestion. However, I don't think the new format will be welcomed by my data reviewers. And, unfortunately being behind more than 1 month is more common than preferred Appreciate you trying to help.

Hello Robert. Thank you for the welcome and your response! I've looked at your array formula and it doesn't seem to result in the number I would expect. I may not have been clear enough in my description. I'll try again. The results I would expect are in cells A3 & A4. The result of 500 in A3 would be generated by summing C3 (110) + E3 (120) + G3 (130) + I3 (140) after determining that D3, F3, H3, and J3 had values. In other words, only sum "Forecasts" if "Actuals" are entered. The result of 360 in A4 would be generated by summing C4 (110) + E4 (120) + G4 (130) but not include I3 (140) after determining that D4, F4, and H4 had values but J3 did not. Hope that provides more clarity and you'll take another crack at it. Thanks again

You did not specify Excel version so I use array to work in all Excel version.(you can use SUMIFS in Excel >03)
Assuming that the second row is just to show the result:
Excel Workbook
ABCDEFGHIJ
120132013JanJanFebFebMarMarAprApr
2ForecastActualForecastActualForecastActualForecastActualForecastActual
3500900110210120220130230140240
4
5
6forecast
7Actual
Sheet1

Edit: Robert Mika is not human

So who is ?

Robert,

That's it! Thank you! You've made my day

Replies
5
Views
265
Replies
2
Views
270
Replies
3
Views
212
Replies
3
Views
388
Replies
7
Views
167

1,203,136
Messages
6,053,699
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back