Summing non-contiguous Conditional Values

Pike36

New Member
Joined
Mar 19, 2022
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
This is my first post; I hope someone can help. I have a labor forecasting tool with a simplified version shown below. The time can span more than a year and there can be 20-30 employees. Each month has values for Forecast Hours, Actual Hours (entered when timecards are submitted) and a flag (EOM) to indicate when the Actual Hours have been entered. Actual Hours could be 0 even though there were forecasted hours. How can I calculate (hopefully no VBA) the Forecast Hour column for each employee? The Actual hours are simply a sum of the actual hours. The "formula" for the Total Forecast Hours is highlighted yellow.

I can handle Excel basics but this is above me. Thank you for your help.

1647696151034.png
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
@Pike36 Welcome.
Maybe like....
Book2
ABCDEFGHIJKLMNO
1JanFebMarApr
2EOMYEOMYEOMEOM
3Forcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursTotal ForcastTotal Actual
4Employee110152025304011040
5Employee2121015025306510
6Employee3182081222257932
Sheet6
Cell Formulas
RangeFormula
N4:N6N4=SUMPRODUCT(B4:L4*(C$2:M$2<>"Y")*(C$3:M$3="Actual Hours"))+SUMPRODUCT(C4:L4*(C$2:L$2="Y"))
O4:O6O4=SUMPRODUCT(C4:L4*(C$3:L$3="Actual Hours"))

Hope that helps.
 
Upvote 0
@Pike36 Welcome.
Maybe like....
Book2
ABCDEFGHIJKLMNO
1JanFebMarApr
2EOMYEOMYEOMEOM
3Forcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursTotal ForcastTotal Actual
4Employee110152025304011040
5Employee2121015025306510
6Employee3182081222257932
Sheet6
Cell Formulas
RangeFormula
N4:N6N4=SUMPRODUCT(B4:L4*(C$2:M$2<>"Y")*(C$3:M$3="Actual Hours"))+SUMPRODUCT(C4:L4*(C$2:L$2="Y"))
O4:O6O4=SUMPRODUCT(C4:L4*(C$3:L$3="Actual Hours"))

Hope that helps.
Thank you for the quick response! I'll give it a try.
 
Upvote 0
Alternatives:

22 03 20.xlsm
ABCDEFGHIJKLMNO
1JanFebMarApr
2EOMYEOMYEOMEOM
3Forcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursTotal ForcastTotal Actual
4Employee110152025304011040
5Employee2121015025306510
6Employee3182081222257932
Hours
Cell Formulas
RangeFormula
N4:N6N4=SUM(B4:L4)-SUMIF(C$2:L$2,"Y",B4:K4)
O4:O6O4=SUMIF(B$3:L$3,$C$3,B4:L4)
 
Upvote 0
@Peter_SSs @Pike36 There is a subtle difference.(Error ? )
Rightly or wrongly, assumed that the entry of Actual Hours might be progressive and not necessarily coincide with the 'Y"confirmation of EOM?
Therefore, my formula updates Total Actual as and when but, prioritises forecast hours over actual, until EOM is confirmed by 'Y'

See below for March. N4 = 110. With Peter's formula it would be 130
Book2
ABCDEFGHIJKLMNO
1JanFebMarApr
2EOMYEOMYEOMEOM
3Forcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursForcast HoursActual HoursTotal ForcastTotal Actual
4Employee11015202530204011060
5Employee2121015025306510
6Employee3182081222257932
Sheet3
Cell Formulas
RangeFormula
N4:N6N4=SUMPRODUCT(B4:L4*(C$2:M$2<>"Y")*(C$3:M$3="Actual Hours"))+SUMPRODUCT(C4:L4*(C$2:L$2="Y"))
O4:O6O4=SUMPRODUCT(C4:L4*(C$3:L$3="Actual Hours"))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top