SUMIF criteria

[lkawecki]

I want to add the numbers in a column that are in cells that are not filled.
I tried the formula: =SUMIF(A2:A5,"no fill") and got the result, 0. A2:A5 is
the range and "no fill" is the criteria. I'm thinking that I'm not using the
correct text for the criteria. Can someone help me?

 

[Max]

Something like this, in say C2: =SUMIF(A2:A5,"no fill",B2:B5)
where A2:A5 contains the text: no fill
and B2:B5 contains the corresponding numbers to be summed
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[lkawecki]

Thanks for your reply, Max. But my problem isn't directly a text problem. It
has to do with the fill color of a cell. When a debit is paid, the cell that
contains the debit's amount is colored. At the end of the month, using the
previous example, say A2 & A4 have been paid, so cells A2 & A4 are yellow;
cells A3 & A5 have not been paid, so those cells contain "no fill". I need a
formula, or macro, to add up all of the cells with no color, "no fill".
Instead of saying, " I'm thinking that I'm not using the correct text for
the criteria." I should've asked if "no fill" is a correct term to use for a
criterion. Besides that, I really, really want to find out how to add my
outstanding debits at the end of each month, automatically. I'm tired of
doing it manually!

 

[David Biddulph]

You can't do it with a formula. It'll need VBA. Plenty of examples and
useful references in the archives of this newsgroup.

 

[Max]

Ah, I see. I took your original posting literally

You could try this sample from my archives:
http://www.savefile.com/files/378485
Count n Sum Cells By FillColor_BobPhillips_ColorIndex_UDF.xls
(nicely rendered, full details)

The sample file contains an implementation of Bob Phillips' ColorIndex
Function from his "Processing Coloured Cells" page at:
http://www.xldynamic.com/source/xld.ColourCounter.html
and some examples on how to use the UDF in Excel
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik