SumIf reference to date

[xmoore]

Guys,

Hope you can help me out.
How can I reflect the correct trainee count for Day1-Batch1?
Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3.
Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day.
Attached herein is the sample file

thanks in advance.

[xmoore]

Guys,

By any chance you have any solution in mind for my dilema?

thanks,
xmoore

[darkyam]

How about

Please Login or Register  to view this content.

in F5, copied over to G5, with just =F5-G5 in H5?

[xmoore]

Appreciate the response Darkyam.

Tried the code but it would only work on a fix number (i.e. Scheduled Trainee).
When I copied the formula over to G5 (actual) and tried to play around with it, the results are uncontrolled.
For instance, if the actual in day2 is 0, the result comes up with 3 if the day1's actual is 6.
I'm trying a different approach by adding columns to hold temporary records.

In line of which, how can I reflect the value of J5 reference to today's date?
Seems like index function only works if the record to display is the top row, not the other way around. or I'm doing something wrong

[darkyam]

You shouldn't have to have changed the formula when you copied it over, unless you copied and pasted the text of the formula. That being said, the formula was intended to take the average of the attendees for all entries on or before today's date. What would you like to see if there were 6 attendees in yesterday's batch, but 0 in today's? 0? 6?

In line of which, how can I reflect the value of J5 reference to today's date?
Seems like index function only works if the record to display is the top row, not the other way around. or I'm doing something wrong

I'm not sure I understand the first line. What do you intend to do exactly?
I don't know exactly how you're trying to use the Index function here, but I can understand your difficulty because you have merged cells. You should try to avoid merged cells if at all possible. Instead, highlight the cells you want to appear merged-->format cells-->Alignment-->under Horizontal tab, pick Center Across Selection.

[xmoore]

If there were 6 attendees in yesterday's training and today's attendees are zero, the actual attendees should reflect zero (0). Since essentially, the said training batch has zero attendees.

I tried to play around with the spreadsheet and came up with the following. I plan to hide some of the column and rows. Any chance you could still optimize it.
For the variance total, What I want to do is to some only the variance2 only upto todays date.

thanks.

[darkyam]

So, it's really the lesser of the two numbers in each training that you want to count?

[xmoore]

yes. it is something like that. Generally, the actual count must reflect the numbers of day2 of each batch, but the initial number of day1 one must reflect as well in the total assuming today's date is day1

[darkyam]

For variance total, you can use =SUMIFS(T7:BF7,$T$6:$BF$6,"Variance2",$R$4:$BD$4,"<="&TODAY())

I don't really understand the point of the tempd column (H). It's just equaling J.

For variance temp, perhaps =K7+IF(INDEX($N$3:$BE$3,MATCH(TODAY(),$N$4:$BE$4,1))="Day1",SUMIF($N$4:$BF$4,"="&TODAY(),P7:BD7),0) will work.

hth

[xmoore]

Appreciate all the help darkyam.
I guess that should do for the variance columns.
As for the tempd1 column I was running few test earlier using that column