Formula for summing a range of cells based on cell color?

**dta1984** · 03-28-2012, 03:22 PM

I am looking for a formula that will sum a range of cells based on a certain color. I have conditional formated certain rows, and i would like a formula that sums the rows in the sheet looking for that color.

**NBVC** · 03-28-2012, 03:25 PM

What is the conditional formula you used?

**dta1984** · 03-28-2012, 04:08 PM

Originally Posted by NBVC

What is the conditional formula you used?

I don't have access to it right now, but it was to color the cell based on the date. The cells from today and previous were colored to show "actual" numbers. Cells from today forward were colored differently to distinguish forecasted numbers.

**NBVC** · 03-28-2012, 04:12 PM

Well, guessing...

Try something like:

=SUMIF(A2:A100,"<="&TODAY,B2:B100)

and

=SUMIF(A2:A100,">"&TODAY,B2:B100)

these will sum values in B2:B100 based on the date in A2:A100 as it compares to Today's date.

If you just want to count cells, use COUNTIF instead, and don't include a third B2:B100 range.

**darknation144** · 03-29-2012, 04:06 AM

Did this work for you?

**dta1984** · 03-29-2012, 10:07 AM

I ended up doing it a different way. I had a summary column at the end of my sheet, so I used a sumproduct formula to sum every 4th row in that column. Seems to have worked. Thanks!