Forum Discussion

H LLOYD's avatar
H LLOYD
Copper Contributor
Aug 23, 2018

Formula Question

I have created a spreadsheet to track and then sum PTO hours relative to a 60 hour pay period. I've used IF statements to assign values (hours) based on key words and am wondering if there is a formula that can identify when a date, which could be in more than one cell, exceeds 10 hours. Example: A1, B1 and C1 have same date but different values; the total of combined cells exceeds 10 and I want those three cells to fill with a color. Thanks!

excel
Formulas and Functions
  • vijaykumar shetye's avatar
    vijaykumar shetye
    Brass Contributor
    Aug 23, 2018

    Hi H LLOYD,

    For the Conditional Formatting of the required cells, follow the below procedure.

     

    Select cells A2 to A16

    Go to Home > Conditional Formatting > New Rule > Use a Formula to determine which cells to Format.

    In the Text box  below Format values where this formula is true, enter the formula

    =SUMIF(A$2:A$16,A2,C$2:C$16)>10

    Format > Fill > Select the required cell background colour.

     

    Change cell references as required.

    Sample Excel file is attached for your reference.

     

    Do let me know if this is what you wanted to do.

     

    Regards,

     

    Vijaykumar Shetye,

    Panaji, Goa, India

    • H LLOYD's avatar
      H LLOYD
      Copper Contributor
      Aug 23, 2018

      It worked! I am so very grateful to you, thank you so much.

      I have another question: based on what you sent me, is there a way to
      for colored cells to reflect -5 for each group of colored cells in
      cell C20? In the example file (attached), cells A7 & A8 = -5 and cells
      A10, A11 & A12 = -5, for a total of -10 to appear in cell C20?

      Thanks again for your time and help.

      Warmly,

      Heather

Resources