excel - Sum cells based on colour and date -

i have following:

date ------- cost

jan      £500 jan      £600 feb      £300 feb      £600 march    £1000 march    £500 

the cost cells coloured differently depending on current status (confirmed green, unconfirmed white, semi-confirmed yellow), need formula sum costs example, green , in february.

i'm aware vba required sort of colour function, , have found useful 1 called colorfunction() allows me sum/count cells of colours using following formula:

colorfunction(a1, b1:b5, false) 

a1 being colour compare range against, , false / true returning sum or count result.

however cannot work custom function month() formula or sumif. over-complicating please point out idiotic mistakes i'm making in trying figure out.

add function vba module in order return cells interior color index:

    function colorindex(rng range, _                     optional text boolean = false) variant     dim cell range, row range     dim long, j long     dim iwhite long, iblack long     dim arycolours variant      if rng.areas.count > 1         colorindex = cverr(xlerrvalue)         exit function     end if      iwhite = whitecolorindex(rng.worksheet.parent)     iblack = blackcolorindex(rng.worksheet.parent)      if rng.cells.count = 1         if text             arycolours = decodecolorindex(rng, true, iblack)         else             arycolours = decodecolorindex(rng, false, iwhite)         end if      else         arycolours = rng.value         = 0          each row in rng.rows             = + 1             j = 0              each cell in row.cells                 j = j + 1                  if text                     arycolours(i, j) = _                       decodecolorindex(cell, true, iblack)                 else                     arycolours(i, j) = _                       decodecolorindex(cell, false, iwhite)                 end if              next cell          next row      end if      colorindex = arycolours  end function   private function whitecolorindex(owb workbook)  dim ipalette long     whitecolorindex = 0     ipalette = 1 56         if owb.colors(ipalette) = &hffffff             whitecolorindex = ipalette             exit function         end if     next ipalette end function  private function blackcolorindex(owb workbook) dim ipalette long     blackcolorindex = 0     ipalette = 1 56         if owb.colors(ipalette) = &h0             blackcolorindex = ipalette             exit function         end if     next ipalette end function  private function decodecolorindex(rng range, _                                   text boolean, _                                   idx long) dim icolor long     if text         icolor = rng.font.colorindex     else         icolor = rng.interior.colorindex     end if     if icolor < 0         icolor = idx     end if     decodecolorindex = icolor end function 

then count of cells color index of 14 (green) use sumproduct follows:

=sumproduct(--(colorindex(b1:b100000)=14),b1:b100000)

this return sum of cells in range b1:b100000 color of 14 (green)

the final example should this:

additionally if prefer sumifs on sumproduct yopu have option of using helper column. in column next cost enter =colorindex(b1) , drag down

then in cells enter formula

=sum(sumifs(b1:b10,c1:c10,14,a1:a10,{"feb","march"}))

replacing months months you'd sum (your oringal title indicates end goal).

this sum cost values helper row says index 14(green) , month either feb or march