SUMIF criteria
 

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?

SUMIF criteria
 

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" wrote:
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?

SUMIF criteria
 

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!


"Max" wrote:

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" wrote:
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?

SUMIF criteria
 

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

"lkawecki" wrote in message
...
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!


"Max" wrote:

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" wrote:
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?

SUMIF criteria
 

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
---
"lkawecki" wrote:
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!