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.

 

[Dave01]

Test file results query

Hi sorry to keep buging you with the same issue, please could you have a look at my test file named "test file results.xls"

I have entered your formula for the results link and I get a N/A, however you dont seem to be getting it.

whats weird is that if both have target in the formula, the target is worked out correctly and so is the Results. It doesnt make sense, would you mind taken a look at my latest upload spreadsheet, and see whats going on.

Ive got to be missing something, I have attached an image showing your formula for results and it shows an N/A.

This is in the AUG tab.

if you click below the error and see the formula youll see the formula both link to target, and it works ??

file is here
test file result


thanks for your help and patience.

dave.

 

[jasonb75]

Comparing your result formula to mine, there is one key difference, hopefully this makes sense.

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

=SUMPRODUCT(($E$4:$BJ$4=Result)*(WEEKDAY($D$3:$BK$3,2)<=5),$E5:$BJ5)

In yours, the weekday range is 2 columns wider than the other ranges. With any formula line this, all ranges need to be equal in size.

E4 and D3 are compared to the criteria and return the value from E5 if applicable, moving along the line, F4 and E3 return F5 then G4, F3 and G5, etc all the way up to BJ4, BI3 and BJ5.
If you look at the cells that I have marked in bold, each is 1 column left of the other 2 in the same group.

Following on past the end of my formula, you would end up at BL4, BK3 and BL5. Because your formula doesn't extend far enough on the first and last ranges, the middle one has nothing to compare to so it results in a #N/A! error.