SOLVED

Excel Attendance Formula Help: Sum of attendance points for all dates w/in 6 months of infraction

%3CLINGO-SUB%20id%3D%22lingo-sub-3258394%22%20slang%3D%22en-US%22%3EExcel%20Attendance%20Formula%20Help%3A%20Sum%20of%20attendance%20points%20for%20all%20dates%20w%2Fin%206%20months%20of%20infraction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3258394%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20add%20sum%20of%20all%20points%20from%20f%20column.%26nbsp%3B%201%20constraint%20are%20Points%20dropping%20off%20after%206%20months%20(180%20days%20plus%201%20inclusive%20date)%20from%20start%20date%2C%20and%20if%20points%20are%20within%20the%20TODAY%20date%2C%20points%20will%20be%20added%20to%20sum%20total.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20same%20sheet%20%22Attendance%20Tracker%22%20I%20added%20a%20segment%20to%20pull%20an%20employee%2Fassociate%2FEE%20(I2)%20and%20Total%20Points%20(k2)%2C%20and%20attempted%20to%20add%20a%20SUMIF%20formula%20but%20it%20error%20outs.%26nbsp%3B%20I%20appreciate%20some%20guidance%20and%20assistance.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20I'll%20need%20the%20points%20to%20pop%20up%20when%20I%20pull%20the%20associate%2Femployee%2FEE%20in%20the%20drop%20box%20in%20I2.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CBHRBP_0-1647405782590.png%22%20style%3D%22width%3A%20687px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F356124iB36CD087D97D7DA2%2Fimage-dimensions%2F687x171%3Fv%3Dv2%22%20width%3D%22687%22%20height%3D%22171%22%20role%3D%22button%22%20title%3D%22CBHRBP_0-1647405782590.png%22%20alt%3D%22CBHRBP_0-1647405782590.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3258394%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3260075%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Attendance%20Formula%20Help%3A%20Sum%20of%20attendance%20points%20for%20all%20dates%20w%2Fin%206%20months%20of%20infractio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260075%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48469%22%20target%3D%22_blank%22%3E%40Martin_Weiss%3C%2FA%3E%20you%20Sir%20are%20a%20true%20wizard%20and%20brilliant!%20Thank%20you%20tremendously!!!!!!!%20Thank%20you%20is%20an%20understatement!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3259943%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Attendance%20Formula%20Help%3A%20Sum%20of%20attendance%20points%20for%20all%20dates%20w%2Fin%206%20months%20of%20infractio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3259943%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1334378%22%20target%3D%22_blank%22%3E%40CBHRBP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edepending%20on%20your%20local%20settings%2C%20it%20might%20be%20that%20you%20need%20to%20replace%20the%20%3B%20with%20%2C%20in%20the%20formula.%3C%2FP%3E%3CP%3ESo%20instead%20of%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIFS(F2%3AF7%3BA2%3AA7%3BI1%3BC2%3AC7%3B%22%26gt%3B%3D%22%26amp%3BTODAY()-181)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eit%20might%20be%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIFS(F2%3AF7%2CA2%3AA7%2CI1%2CC2%3AC7%2C%22%26gt%3B%3D%22%26amp%3BTODAY()-181)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20other%20thing%20is%2C%20that%20you%20need%20to%20adjust%20the%20cell%20references%20to%20the%20real%20situation%20in%20your%20worksheet.%20When%20I%20look%20at%20your%20screenshot%2C%20it%20should%20be%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIFS(F4%3AF10%2CA4%3AA10%2CI2%2CC4%3AC10%2C%22%26gt%3B%3D%22%26amp%3BTODAY()-181)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3258707%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Attendance%20Formula%20Help%3A%20Sum%20of%20attendance%20points%20for%20all%20dates%20w%2Fin%206%20months%20of%20infractio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3258707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48469%22%20target%3D%22_blank%22%3E%40Martin_Weiss%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETY.%26nbsp%3B%20%26nbsp%3BI%20got%20an%20error%20I%20tried%20the%20formula%20but%20noticed%20it%20was%20in%20the%20wrong%20cell%2Fs%20so%20I%20modified%20but%20either%20didn't%20work.%26nbsp%3B%20%26nbsp%3BError%20came%20up.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CBHRBP_0-1647435763675.png%22%20style%3D%22width%3A%20700px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F356192i0BF0D4BE45807827%2Fimage-dimensions%2F700x329%3Fv%3Dv2%22%20width%3D%22700%22%20height%3D%22329%22%20role%3D%22button%22%20title%3D%22CBHRBP_0-1647435763675.png%22%20alt%3D%22CBHRBP_0-1647435763675.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3258456%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Attendance%20Formula%20Help%3A%20Sum%20of%20attendance%20points%20for%20all%20dates%20w%2Fin%206%20months%20of%20infractio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3258456%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1334378%22%20target%3D%22_blank%22%3E%40CBHRBP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20could%20try%20it%20with%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DTE_0-1647415771819.png%22%20style%3D%22width%3A%20556px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F356138i2DA434486160B1C6%2Fimage-dimensions%2F556x146%3Fv%3Dv2%22%20width%3D%22556%22%20height%3D%22146%22%20role%3D%22button%22%20title%3D%22DTE_0-1647415771819.png%22%20alt%3D%22DTE_0-1647415771819.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DSUMIFS(F2%3AF7%3BA2%3AA7%3BI1%3BC2%3AC7%3B%22%26gt%3B%3D%22%26amp%3BTODAY()-181)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need a formula to add sum of all points from f column.  1 constraint are Points dropping off after 6 months (180 days plus 1 inclusive date) from start date, and if points are within the TODAY date, points will be added to sum total.   

 

On the same sheet "Attendance Tracker" I added a segment to pull an employee/associate/EE (I2) and Total Points (k2), and attempted to add a SUMIF formula but it error outs.  I appreciate some guidance and assistance.  

 

And then I'll need the points to pop up when I pull the associate/employee/EE in the drop box in I2.   

 

CBHRBP_0-1647405782590.png

 

4 Replies

Hi @CBHRBP 

 

you could try it with this formula:

 

DTE_0-1647415771819.png

=SUMIFS(F2:F7;A2:A7;I1;C2:C7;">="&TODAY()-181)

@Martin_Weiss 

 

TY.   I got an error I tried the formula but noticed it was in the wrong cell/s so I modified but either didn't work.   Error came up.  

 

CBHRBP_0-1647435763675.png

 

I really appreciate you!

best response confirmed by CBHRBP (New Contributor)
Solution

Hi@CBHRBP 

 

depending on your local settings, it might be that you need to replace the ; with , in the formula.

So instead of 

=SUMIFS(F2:F7;A2:A7;I1;C2:C7;">="&TODAY()-181)

it might be

=SUMIFS(F2:F7,A2:A7,I1,C2:C7,">="&TODAY()-181)

 

The other thing is, that you need to adjust the cell references to the real situation in your worksheet. When I look at your screenshot, it should be 

=SUMIFS(F4:F10,A4:A10,I2,C4:C10,">="&TODAY()-181)

 

@Martin_Weiss you Sir are a true wizard and brilliant! Thank you tremendously!!!!!!! Thank you is an understatement!