Trying to figure out how I can sumifs the total "target" figures for weekdays, and the total "target" figures for " Sat" & "sun"

[Dave01]

Hi,

I built a target and result for our purchase ledger team, which worked really well as it only considered working days throughout the month daily. The AP Manager would enter a target figure into the green sections, then enter the actually result of the number processed in the Result column, this would be done daily.

Total targets for the month would just look for the columns called Target and add up the row, the same applies for the Result. This worked really well as it was set up Monday to Friday.
Now he wants to extend this to Saturday and Sunday, but wants to record the target figure and Result figures separate to the weekday figures, and now Im stuck on how to separate the sumifs as before it would just add the row up for target and results.

The spreadsheet can be found here This spreadsheet template

so

data is entered in the green sections under Target and Result
the totals are added up in the blue sections

Weekend doesnt work - but the total will include both Sat & Sunday for both Target and Result

Please could you help resolve the weekend formula

Thaks

David.

 

[jasonb75]

There is no realistic way to identify weekday using SUMIF(s) or similar functions, SUMPRODUCT is probably the easiest way to do what you need.

This formula will pull the target figure for weekdays and can be easily adjusted for the other 3 columns.

=SUMPRODUCT(($D$4:$BI$4='target Template.xlsx'!Target)*(WEEKDAY($D$3:$BI$3,2)<=5),D5:BI5)

<=5 for weekdays, >=6 for weekends.

 

[Dave01]

works fantastic thank you, spend the last fe hours trying to get it to do that.

 

[Dave01]

There is no realistic way to identify weekday using SUMIF(s) or similar functions, SUMPRODUCT is probably the easiest way to do what you need.

This formula will pull the target figure for weekdays and can be easily adjusted for the other 3 columns.

=SUMPRODUCT(($D$4:$BI$4='target Template.xlsx'!Target)*(WEEKDAY($D$3:$BI$3,2)<=5),D5:BI5)

<=5 for weekdays, >=6 for weekends.

It works really well, but I am not understanding how it links

=SUMPRODUCT(($D$4:$BI$4=Target)*(WEEKDAY($D$3:$BI$3,2)>=6),D5:BI5)


I moved it over to my live spreadsheet and I can see that its linked to the Target Column, when I move it to the results column, it calculated the results column correctly, but I dont understand why as the target column is named target, but the results column is called results.

can you explain why its working?

thanks

david

 

[Dave01]

Ok sorry what I meant was why is the formula working in both columns but the = target does not change. If i do change it to result it doesn't work.

 

[jasonb75]

I didn't make the column on the results absolute, which means that when you drag the formula it offsets the values by one column. This wasn't actually intentional, but it works better because I forgot to allow for your merged columns.

If you want to set the criteria correctly for clarity then you would need to use the following, notice that the ranges are different in the second formula.

=SUMPRODUCT(($D$4:$BI$4='target Template.xlsx'!Target)*(WEEKDAY($D$3:$BI$3,2)<=5),$D5:$BI5)

=SUMPRODUCT(($E$4:$BJ$4='target Template.xlsx'!Result)*(WEEKDAY($D$3:$BI$3,2)<=5),$E5:$BJ5)

When you merge 2 or more cells, only the one in the top right corner contains a value for formula purposes, the rest are empty. This means that to get the result from BI5 you have to compare the word Result in BI4 to the date in BH3, if the formula looks at BH4 for the date it will see nothing.

 

[Dave01]

Hi, Thanks for coming back, I tried changing the formula to include the Results, but when I change the formula to Results it either comes up blank a zero or causes an error. For some reason and I can not figure out is why if you reference to Target, both the Target and Results calculate correctly. Its like using an If statement saying if its under target, sum all the target and if its not, sum all the results.

Ive been playing around with it since last night, but the result reference just wont work.


Heres a copy of the updated version, look at the Aug tab, and youll see what I mean, Im stumped on why it works, because the Results column just shouldnt work.


New updated spreadsheet Here

Really thankful for your help here.

 

[jasonb75]

Not sure what you've done, but using the formulas in post 6 the results appear to be correct.

Note that 2 of the ranges are different in the result formula to allow for the dates being in merged cells.

test.xlsx
	BL	BM	BN	BO	BP	BQ	BR	BS	BT
3	AP Processor	Weekend totals for Month		Daily Totals for the Month		Total Achievement		Result Variance	Overall Result Achieved
4		Target	Result	Target	Result	Target	Result
5	AP Clarks	60	30	540	371	600	401	(199)	Under Target
6	AP Clarks			400	348	400	348	(52)	Under Target
7	AP Clarks			100	36	100	36	(64)	Under Target
8	AP Clarks	50	80	800	749	850	829	(21)	Under Target
9	AP Clarks								Matched
10	AP Clarks			800	662	800	662	(138)	Under Target
11	AP Clarks			100	80	100	80	(20)	Under Target
12	AP Clarks			700	722	700	722	22	Over Target
13	AP Clarks				22		22	22	Over Target
14	AP Clarks			800	932	800	932	132	Over Target
15	AP Clarks			500	578	500	578	78	Over Target
16	AP Clarks			800	751	800	751	(49)	Under Target
17	AP Clarks								Matched
18	Kristina			250	258	250	258	8	Over Target
19	Maria			150	279	150	279	129	Over Target
20	AP Clarks			50	181	50	181	131	Over Target
21	AP Clarks			50	52	50	52	2	Over Target
22	AP Clarks				83		83	83	Over Target
23
24	Totals	110	110	6040	6104	6150	6214	64	Over Target
Aug

Cell Formulas
Range	Formula
BL5:BL22	BL5	=A5
BM5:BM22	BM5	=SUMPRODUCT(($D$4:$BI$4='target Template.xlsx'!Target)*(WEEKDAY($D$3:$BI$3,2)>=6),$D5:$BI5)
BN5:BN22	BN5	=SUMPRODUCT(($E$4:$BJ$4='target Template.xlsx'!Result)*(WEEKDAY($D$3:$BI$3,2)>=6),$E5:$BJ5)
BO5:BO22	BO5	=SUMPRODUCT(($D$4:$BI$4='target Template.xlsx'!Target)*(WEEKDAY($D$3:$BI$3,2)<=5),$D5:$BI5)
BP5:BP22	BP5	=SUMPRODUCT(($E$4:$BJ$4='target Template.xlsx'!Result)*(WEEKDAY($D$3:$BI$3,2)<=5),$E5:$BJ5)
BQ5:BR22	BQ5	=BM5+BO5
BS5:BS22	BS5	=IF(BR5>BQ5,BR5-BQ5,-BQ5--BR5)
BT5:BT22	BT5	=IF(BS5>0,OT,IF(BS5=0,Match,UT))
BM24:BS24	BM24	=SUM(BM5:BM23)
BT24	BT24	=IF(BS24>0,Maintenance!$E$2,IF(BS24=0,Maintenance!$E$4,Maintenance!$E$3))

 

[Dave01]

HMM this is strange, if you look at the file test, I tried to put your Result formula in, and they are exactly the same as yours, but it didnt work, keeping them both as target worked great, for both Target and Results.

test file here

 

[jasonb75]

Not sure if it's the wrong link or of you forgot to save it but test file linked in your last reply still contains the Target formula in both and the results as in my previous post.