locked
Need Excel 365 formula to sum amounts in colored cells RRS feed

  • Question

  • Hi,

    I have some amounts in column A, B & C for eg., and some of the amounts are yellow colored, I need an excel 365 formula [w/o vba if possible] to sum the amounts colored yellow and if the color is removed the total should decrease by that amount.

    Your help will be highly appreciated.

    raaj.

    Sunday, November 1, 2020 11:37 AM

All replies

  • Assume sheet is named 'Sheet1', column C is the data, and assume D is available for a helper column.

    Select D2, define a name CellLeftInteriorColor with the formula

    =GET.CELL(63,Sheet1!C2)+NOW()*0

    Then in D2 and below enter =CellLeftInteriorColor 

    This returns the color code. The +NOW()*0 is to get it to recalculate if you make any data change. BUT it does NOT update if you only change the colour, you need to trigger a recalculation. 

    The GET.CELL() function is in the old XLM macro language and is only available in Names, not directly in cell formulas.

    https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color

    Wednesday, November 18, 2020 5:51 PM