SUMIF with Multiple Conditions

[SaParekh58]

I am looking for an equation that will provide an updated projection of labor hours and expenses.
I will use the picture below in this example.

At the beginning of the week, I scheduled Randall to work 7.5 hours on Sunday, 7.5 hours on Monday, and 9.5 hours on Tuesday (forecasted total hours worked = 24.5).

On Sunday, Randall actualizes 7 hours lowering his overall projected hours by .5 to 24 hours (Sunday 7 actualized hours + Monday 7.5 projected hours +Tuesday 9.5 projected hours)
On Monday, Randall actualizes 9 hours increasing his overall projected hours by 2 to 26 hours (Sunday 7 actualized hours + Monday 9 actualized hours + Tuesday 9.5 projected hours)

I am looking for a formula so that as I enter the day-to-day actualized numbers, my projection will be updated accordingly.

You will notice that I tried to use a "Complete" box to trigger the formula.
The idea was that once I entered the actualized data for that day, I would enter an "X" in the box which would trigger the system to use the actual data from that day and days past and add it to the forecasted hours in the future.

In the example shown, I have entered an "X" for both Sunday and Monday.
So the formula I would need here would account for the following;
Updated Projected Hours = (Actual Hours on Sunday) + (Actual Hours on Monday) + (Forecasted Hours on Tuesday)

Thank you in advance for your time.

 

[Eric W]

Something like:

Book2
	A	B	C	D	E	F	G	H	I
1		Sunday		Monday		Tuesday		Weekly Totals
2		Forecast	Actual	Forecast	Actual	Forecast	Actual	Forecast	Updated Proj.
3	Name	Hours	Hours	Hours	Hours	Hours	Hours	Hours	Hours
4	Randell	7.5	7	7.5	9	9.5		24.5	25.5
5		Complete	x	Complete	x	Complete
Sheet9

Cell Formulas
Range	Formula
H4	H4	=B4+D4+F4
I4	I4	=SUMIF(C5:G5,"",B4:F4)+SUMIF(C5:G5,"X",C4:G4)

This assumes you'll always have "Complete" in the B/D/F cells, otherwise the formula will include some erroneous values. But it's easy enough to adjust if that's not the case. The H2 formula can be adapted if you have more columns. The I4 formula can just look to see if you've entered a value in the C4/E4/G4 cells without need of the X on row 5. But take a look and see if this will work for you, we can tweak it as you want.

 

[SaParekh58]

	SUN					MON						TUE						WED						THU						FRI						SAT							WEEKLY TOTALS
	FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		ACTUAL				FORECAST		PROJECTION
Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense
Rosa F	5	$ 75.00	0	$ -		Rosa F	5	$ 75.00	5	$ 75.00		Rosa F		$ -	0	$ -		Rosa F		$ -	0	$ -		Rosa F	4.5	$ 67.50	0	$ -		Rosa F		$ -	0	$ -		Rosa F		$ -	0	$ -		Rosa F	14.5	$ 217.50	?	$ -
Evelin		$ -	4.67	$ 65.38		Evelin		$ -	0	$ -		Evelin	4.5	$ 63.00	5	$ 70.00		Evelin	4.5	$ 63.00	0	$ -		Evelin		$ -	0	$ -		Evelin	5	$ 70.00	0	$ -		Evelin	5	$ 70.00	0	$ -		Evelin	19	$ 266.00	?	$ -
Erick	5.5	$ 68.75	5.5	$ 68.75		Erick		$ -	0	$ -		Erick	5.5	$ 68.75	8	$ 100.00		Erick	5.5	$ 68.75	0	$ -		Erick	5.5	$ 68.75	0	$ -		Erick		$ -	0	$ -		Erick	5.5	$ 68.75	0	$ -		Erick	27.5	$ 343.75	?	$ -
Ana		$ -	0	$ -		Ana		$ -	0	$ -		Ana	4.5	$ 54.00	2	$ 24.00		Ana	4.5	$ 54.00	0	$ -		Ana		$ -	0	$ -		Ana		$ -	0	$ -		Ana		$ -	0	$ -		Ana	9	$ 108.00	?	$ -
	10.5	$ 143.75	10.17	$ 134.13			5	$ 75.00	5	$ 75.00			14.5	$ 185.75	15	$ 194.00			14.5	$ 185.75	0	$ -			10	$ 136.25	0	$ -			5	$ 70.00	0	$ -			10.5	$ 138.75	0	$ -			70	$ 935.25	0	$ -
COMPLETE	X					COMPLETE	X					COMPLETE	X					COMPLETE						COMPLETE						COMPLETE						COMPLETE

Thank you for your response.
The version I provided was very modified to what I am actually using, and therefore the formulas did not work.
Rather I uploaded an example that looks more like the spreadsheet I am using.
I am most concerned with the projection of hours, as I can figure out expenses from that.
In this example, I have uploaded the data through Tuesday and want to know what the new projected hours would be provided that the employee sticks to their schedule on all future days.
I also was hoping that the formula would automatically update after I put in actualized data for the next day (ie. for Wednesday).
This is why I added the "Complete" component, hoping to trigger the formula to know that.
The overall projection can be found in the rightmost column.
Thank you again for your help here, as I am completely stumped on how to do this.

 

[Eric W]

Two preliminary comments. First, if you simplify your situation too much, you'll end up getting a response that doesn't work for your real workbook. You should try to include at least a few rows, with all the columns. Next, try to use the XL2BB tool when posting sample sheets. Pasting an image like that does not let the helpers start working on your problem. It took me quite a while to recreate your spreadsheet, and I probably wouldn't have bothered if I hadn't already been working on the problem. XL2BB makes it as easy as click/paste.

On to the problem:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1		SUN						MON						TUE						WED						THU						FRI						SAT						WEEKLY TOTALS
2		Forecast		Actual				Forecast		Actual				Forecast		Actual				Forecast		Actual				Forecast		Actual				Forecast		Actual				Forecast		Actual				Forecast		Projection
3	Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense		Name	Hours	Expense	Hours	Expense
4	Rosa F	5	$ 75.00	0	$ -		Rosa F	5	$ 75.00	4	$ 60.00		Rosa F			0			Rosa F						Rosa F						Rosa F						Rosa F						Rosa F	10	$ 150.00	4	$ 60.00
5	Evelin		$ -	4.67	$ 65.38		Evelin						Evelin	4.5	$ 63.00	5	$ 70.00		Evelin	4.5	$ 63.00	4	$ 56.00		Evelin						Evelin						Evelin						Evelin	9	$ 126.00	13.67	$ 191.38
6	Erick	5.5	$ 68.75	5.5	$ 68.75		Erick						Erick	5.5	$ 68.75	8	$ 100.00		Erick	5.5	$ 68.75	5.5	$ 68.75		Erick						Erick						Erick						Erick	16.5	$ 206.25	16.5	$ 206.25
7	Ana			0	$ -		Ana						Ana	4.5	$ 54.00	2	$ 24.00		Ana	4.5	$ 54.00	5	$ 60.00		Ana						Ana						Ana						Ana	9	$ 108.00	9	$ 108.00
8		10.5	143.75	10.17	$ 134.13			5	$ 75.00	4	$ 60.00			14.5	$ 185.75	15	$ 194.00			14.5	$ 185.75	14.5	$ 184.75			0	$ -	0	$ -			0	$ -	0	$ -			0	$ -	0	$ -			44.5	$ 590.25	43.17	$ 565.63
9
10	Complete	x					Complete	x					Complete						Complete						Complete						Complete						Complete
Sheet9

Cell Formulas
Range	Formula
AR4:AS7	AR4	=SUMPRODUCT(B4:AL4,--(MOD(COLUMN($B4:$AL4),6)=2))
AT4:AU7	AT4	=SUM(IF(MOD(COLUMN($B4:$AL4),6)=2,IF($B$10:$AL$10="x",D4:AN4,B4:AL4)))
AL8:AO8,AF8:AI8,Z8:AC8,T8:W8,N8:Q8,H8:K8,B8:E8,AR8:AU8	AR8	=SUM(AR3:AR7)
Press CTRL+SHIFT+ENTER to enter array formulas.

This seems to do the trick. The AR4 formula (which you enter in AR4, then copy it to the rest of AR4:AS7) calculates the weekly totals. The AT4 array formula you enter the same way. It takes the totals from the Forecast columns, unless the X is marked in row 10, then it takes the totals from the Actual columns.

 

[SaParekh58]

Thank you for the feedback.

I will be sure to do a little better job explaining the next time I need some assistance.
As for the XL2BB tool, I did download the add-in and thought I had submitted the graph using this method.
I am therefore not sure why it didn't upload accordingly.

Thank you for the assistance again.
The formula you sent me did work well and solved my needs.

Have a great night.