SUMIF test for blank/empty cell????

[Fred Holmes]

Excel 2000

In common notation, the formula in cell I147 is
=SUMIF($F$1:$F$361,F147,$G$1:$G$361)

Or in R1C1 notation that I am using
=SUMIF(R1C6:R361C6,RC[-4],R1C7:R361C7)

The above provide checkbook subtotals by accounting category, where
the "accounting category" is entered in Column F (Column 6), and the
dollar amount is in Column G (Column 7). The formula works fine so
long as there is explicit "accounting category" text in Column F(6),
but fails when the cell in column F (6) is blank/empty. Is there any
way that I can modify the formula so that it will give the subtotal of
all of the dollar amounts for which the Accounting Category has been
left blank (cell is empty).

I could, of course, put some dummy text, e.g., simply "z", in all of
the cells that are now blank/empty, but I'd rather not do that.

Many thanks,

Fred Holmes

 

[David McRitchie]

Hi Fred,

Interesting, problem, a text 3 compares equal to number 3,
but won't touch blank cells in your formula.

The change to TRIM works for blanks but then those with
a space will not work. So the cure may be worse than the
problem, Unless you use an Event macro to make sure that
items in I147 are automatically trimmed.
I147: =SUMIF($F$1:$F$361,TRIM(F147),$G$1:$G$361)

to install the following event macro right click on the sheet tab
View code, and insert immediately after "Option Explicit"
which you should have. More information on Event macros in
http://www.mvps.org/dmcritchie/excel/excel.htm

One disadvantage of an event macro is that it may be tied into specific
columns like the following is. The macro will not know if you
inserted, deleted or moved columns around on your worksheet.

The macro will convert text strings that look like numbers to numbers.
Don't know if that would matter to you. Your description implies
wording.

Private Sub Worksheet_Change(ByVal Target As Range)
'This will convert 0003 to a number 3
If Target.Column <> 6 Then Exit Sub 'col 6 is "F"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 6 Then
If Not Target.HasFormula Then
Target.Value = Trim(Target.Value)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

You might want to run the TRIMALL macro once to start with
http://www.mvps.org/dmcritchie/excel/joint.htm#trimall
to make sure you are starting out with what you think you see.
The change event macro will take care of subsequent entries.

 

[Fred Holmes]

David,

The accounting categories I'm useing are all text. None are numeric.
In fact, the data in that column is formatted as text, just to be
sure. The categories are relatively few, and the text is generally
mnemonic. The reason for wanting to sum the "other" (accounting
category is blank) items is to run a consistency check, i.e., that the
total of subtotals equals the total of the listed items individually.
To do that, of course, every listed item must be aggregated in one and
only one subtotal. I've also now discovered SUMPRODUCT, which may do
what I want more elegantly.

The macro you did provide has given me some ideas, some things to try.
Many thanks for your help.

Fred Holmes

Hi Fred,

Interesting, problem, a text 3 compares equal to number 3,
but won't touch blank cells in your formula.

The change to TRIM works for blanks but then those with
a space will not work. So the cure may be worse than the
problem, Unless you use an Event macro to make sure that
items in I147 are automatically trimmed.
I147: =SUMIF($F$1:$F$361,TRIM(F147),$G$1:$G$361)

to install the following event macro right click on the sheet tab
View code, and insert immediately after "Option Explicit"
which you should have. More information on Event macros in
http://www.mvps.org/dmcritchie/excel/excel.htm

One disadvantage of an event macro is that it may be tied into specific
columns like the following is. The macro will not know if you
inserted, deleted or moved columns around on your worksheet.

The macro will convert text strings that look like numbers to numbers.
Don't know if that would matter to you. Your description implies
wording.

Private Sub Worksheet_Change(ByVal Target As Range)
'This will convert 0003 to a number 3
If Target.Column <> 6 Then Exit Sub 'col 6 is "F"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Column = 6 Then
If Not Target.HasFormula Then
Target.Value = Trim(Target.Value)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

You might want to run the TRIMALL macro once to start with
http://www.mvps.org/dmcritchie/excel/joint.htm#trimall
to make sure you are starting out with what you think you see.
The change event macro will take care of subsequent entries.

Excel 2000

In common notation, the formula in cell I147 is
=SUMIF($F$1:$F$361,F147,$G$1:$G$361)

Or in R1C1 notation that I am using
=SUMIF(R1C6:R361C6,RC[-4],R1C7:R361C7)

The above provide checkbook subtotals by accounting category, where
the "accounting category" is entered in Column F (Column 6), and the
dollar amount is in Column G (Column 7). The formula works fine so
long as there is explicit "accounting category" text in Column F(6),
but fails when the cell in column F (6) is blank/empty. Is there any
way that I can modify the formula so that it will give the subtotal of
all of the dollar amounts for which the Accounting Category has been
left blank (cell is empty).

I could, of course, put some dummy text, e.g., simply "z", in all of
the cells that are now blank/empty, but I'd rather not do that.

Many thanks,

Fred Holmes

 

[David McRitchie]

Hi Fred,
The formula is probably all you need, the trim macro and event
macro are only needed if someone is prone to placing spaces
in a cell.

--David McRitchie

 

[David McRitchie]

BTW, the formula could just as well be
=SUMIF(F:F,TRIM(F9),G:G)