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

[krapley]

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>

 

[Robert Mika]

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
	A	B	C	D	E	F	G	H	I	J
1	2013	2013	Jan	Jan	Feb	Feb	Mar	Mar	Apr	Apr
2	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual
3	500	900	110	210	120	220	130	230	140	240
4	360	660	110	210	120	220	130	230	140
5
6
7	3120	All actual
8	Actual
9	Feb	440
Sheet4

Cell Formulas
Range	Formula
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.

 

[Piktro]

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

 

[krapley]

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

 

[krapley]

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.

 

[Piktro]

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

[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.

 

[krapley]

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.

 

[Robert Mika]

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
	A	B	C	D	E	F	G	H	I	J
1	2013	2013	Jan	Jan	Feb	Feb	Mar	Mar	Apr	Apr
2	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Actual
3	500	900	110	210	120	220	130	230	140	240
4
5
6	forecast
7	Actual
Sheet1

Edit: Robert Mika is not human

So who is ?

 

[krapley]

Robert,

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

 

[Robert Mika]

Robert,

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

You are welcome.