Counting occurences if between specific date range

[taylorsm]

I have an attendance tracker and I want to be able to select a date range and then have the sum available. The Data Validation worksheet shows the value each has, but i've included below for reference too. The table at the top does something similar, it counts a point if it is within the last six months and assigns a point based on the code. The worksheet "Aaron" is where I have comments/notes and such to help. The pivot table would be replaced with your formula. It is just for show.

So basically, I need the formula to look between the range of dates, and if an occurrence is listed, add them together. Since there are only three events with points, then i guess you could ignore the other letter codes.



UA 1
40 0
AL 0.5
DR 0
GP 0
LE 0.5
PTO 0
SU 0
UA+ 0
UE 0

[kvsrinivasamurthy]

Pl explain manually showing the calculation details how the above points are arrived.

[taylorsm]

I'm not 100% sure on what he exactly are requesting, so please let me know if my answer doesn't help. Points are totaled based on if they fall within the date range. A manager manually inputs if an employee was absent or late or left early. When the manager selects UA/AL/LE then a point value is assigned. Every UA is 1 point, every AL or LE is 1/2 point.

[taylorsm]

It would look something like this: =SUMIFS(LE22:LJ22,LE21:LJ21,">="&B22,LE21:LJ21,"<="&C22)

issue is that it needs to recognize the letter code ("AL") a know to make that a .5

I found this online, =SUM(COUNTIF(LE22:LJ22,{"UA","AL","LE"})*{1,0.5,0.5}), and it works but it requires a date range criteria now

[taylorsm]

There may be a better way (sure there is), but because no one else has been able to help so far, i came up with this.

=SUM(SUM(COUNTIFS(LE22:LJ22, "="&E7, LE21:LJ21,">="&B22, LE21:LJ21,"<="&C22)),SUM(COUNTIFS(LE22:LJ22, "="&F7, LE21:LJ21,">="&B22, LE21:LJ21,"<="&C22)*0.5), SUM(COUNTIFS(LE22:LJ22, "="&G7, LE21:LJ21,">="&B22, LE21:LJ21,"<="&C22)*0.5))

anyone have something better/faster?

[kvsrinivasamurthy]

Try this
In B25

Please Login or Register  to view this content.

In C25

Please Login or Register  to view this content.