SUMIF test for blank/empty cell????

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
0
fsh (33)
10/11/2005 2:50:29 PM
excel 39879 articles. 2 followers. Follow

4 Replies
408 Views

Similar Articles

[PageSpeed] 27

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Fred Holmes" <fsh@his.com> wrote in message news:ugjnk1pnjmdnqn07kse84vc61f08rc3kg8@4ax.com...
> 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


0
10/12/2005 2:38:14 PM
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

On Wed, 12 Oct 2005 10:38:14 -0400, "David McRitchie"
<dmcritchie_xlmvp@verizon.net> wrote:

>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.
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
>My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
>"Fred Holmes" <fsh@his.com> wrote in message news:ugjnk1pnjmdnqn07kse84vc61f08rc3kg8@4ax.com...
>> 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
>
0
fsh (33)
10/12/2005 8:21:17 PM
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



0
10/12/2005 8:39:58 PM
BTW,   the formula could just as well be
    =SUMIF(F:F,TRIM(F9),G:G)


0
10/12/2005 8:46:08 PM
Reply:

Similar Artilces:

How do I compare data in two worksheets to find matching cells?
Each day I work with two worksheets. One is a list of numbers that is given to me by someone else. The other list is something I make myself, again it is a list of numbers. I need to know if there is a way I can compare the data to find out if any of the numbers I input on my list match up to the ones on the list I am given. I am aware of the "Find" function, but I'm working with lists of hundreds of numbers, and it's really annoying to take each one and "find" it on another worksheet. Any answers? Hi Gary, See Chip Pearson's Tagging Duplicates page. In ...

What is a cell mark and what does it look like?
I was working with cells while editing and I need to know what a cell mark is and/or looks like to continue. I am working with Office 2000 Professional. Hi Kira, I am not sure what is this "cell mark" could it be a add comment to a cell? or you just a cell address which refer to A1 - A stands for column and 1 stands for row usually when you select any cell in excel it indicates where you are? Goto pulldown menu option you will see Row & Column Headers see where your cursor lands. "Kira" wrote: > I was working with cells while editing and I need to know wha...

Test your knowledge here.
Suppose there is a number is 100 Problem 1: The amount is decrease by 2% Problem 2: The amount increase to 120 Conditions: 1. No hard-coding. 2. Use just ONE formula. This is to ensure that th formula created is flexible. Can the formula increase the price by 6% Can the formula decrease 100 to 80? Thanks. Your help is highly appreciated -- accountingfrea ----------------------------------------------------------------------- accountingfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2551 View this thread: http://www.excelforum.com/showthread.php?threadid=...

comparing cells in one column to another
I have a column on a spreadsheet and an array of strings. I need to look at the first cell on the spreadsheet and find a matching value in the array of strings. This is how I am doing that With ThisWorkbook.Worksheets(Tracker).Range("Order_Number_Header") For E = 0 To Total_Rows_On_Spreadsheet - 1 For F = 0 To Total_Elements_In_Array - 1 If .Offset(E, 0) = FTS_Order_Number(F) Then msgbox"I Have a Match end if next F next E end with Nothing is matching and I know there are some matches. When I display the value in the fields I see "12345678" for both fields. The f...

blank two fold 8 1/2 x 14 brochure templates
where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure template It is a simple page setup. Setup your printer for legal stock and then your page. In the arrange menu select two or three columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "kawika" <kawika@discussions.microsoft.com> wrote in message news:E99D8D0A-E6C8-48A2-A5AF-C5B6E365219C@microsoft.com... > where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure > template ...

Why won't ADDRESS work nested inside SUMIF?
If I do this: =ADDRESS(4,MATCH(E37,A2:JW2,0)) I get: $L$4 If I do: =SUMIF(A3:JW3,"Units",ADDRESS(4,MATCH(E37,A2:JW2,0)):JW4) I get an error. However if I do: =SUMIF(A3:JW3,"Units",INDIRECT(G1):JW4) I get the answer I want. However, I'd prefer to not have a helper cell. Please help! Some lateral thoughts, with emphasis on resolving the core issues you raised in your other, earlier thread Let's say you have col data in groups of 3 cols, with headers in row2 (eg: Period1, Period2, etc), where each header is placed only in the l...

Mass change of same cell in multiple files??????
I have about 180 files that are price pages from a company we sell products from. The same cell in every file, Sheet 1 P1 is a factor that is used in formulas through out the files. I guess I am wondering if there are 1 of 2 solutions. 1) Can I mass change that P1 cell in all 180 files to be a different number instead of 1.0? or 2) Can I mass change any reference to that cell in any sheet to point to one cell that we can change anytime and only do 1 update for all 180 files? Thanks in advance Hi Jim You can do this with a macro http://www.rondebruin.nl/copy4.htm You can use a form...

Cells losing formatting
This is driving me nuts! I use excel 2000. From time to time certain cells (usually blocks of cells or an entir row) containing numerical data lose all formatting. Excel no longe recognizes the data as numbers and will not sort them etc. Selectin the cell(s) and changing the formatting has no effect. Copying, the "paste special / values" only works sometimes. Manually retyping th data over the old usually works but I am dealing with a spreadshee with about 1000 records. I just had this happen with a row containing date / time stamps. Each cell has an entry like "7/26/04 9:5...

How do I delete the test documents in my recent documents?
I took the test for the excel spreadsheets and now they are in my recent documents and I want to know how to remove them, can you help me? Go into Options and set your MRU list to zero. Close Excel and restart. Reset the list to whatever you like. Gord Dibben MS Excel MVP On Sat, 13 Feb 2010 11:17:01 -0800, mrjack <mrjack@discussions.microsoft.com> wrote: >I took the test for the excel spreadsheets and now they are in my recent >documents and I want to know how to remove them, can you help me? ...

Navigating to Referenced Cell
I run Excel 2002 with SP-2. I finally discovered how to move to a referenced cell ( Control +[ ). I saw somebody else do it with a simple double click of a cell that had a reference in it. Mine, however, doesn't work that way (is there a way to make the double click method work?). The real question is this: After I get to a referenced cell with Control +[ is there a way to QUICKLY get back to where I started? Jeannine I'm surprised you didn't try the obvious: <Ctrl> <]> BTW, double click should work if you; <Tools> <Options> *UNCHECK* "Edit Di...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

Test Message
08/01/2010 02:48:29 p.m. ...

Extracting text from cells
Can anyone suggest a formula to use to extract certain text from a cell? The text may be found anywhere in a single cell, it may vary in length and not always preceded by punctuation, however, it will always be preceded by a "M0" or "P0" . Here's an example: CCN 02/04/04, PAID 05/28/03, M02-10708, BOX AE-3912, DD 01/31/08. The task is to extract just the text, "M02-10708". -- AusTexRich Hi: Use the text function FIND in conjunction with LEFT, RIGHT or MID functions to extract it. However, this might prove to be difficult since you say that what you...

i want to set conditional format to cells containing odd numbers
i want to set conditional format to cells containing odd numbers 1. Select the cell/Range (say A1:A10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and enter the below formula =MOD(A1,2) 4. Click Format Button>Pattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tab>Styles>Conditional Formatting>Manage rules>New...

Possible to "rotate" range of cells so columns are rows and vice versa?
Is it possible to select a rectangular range of cells and then generate a copied range of cells that is a rotation of the first range, where the columns are the rows, and vice versa? Maybe copy, then paste special and select transpose Regards, Peo Sjoblom davidmichaelkarr@gmail.com wrote: > Is it possible to select a rectangular range of cells and then generate > a copied range of cells that is a rotation of the first range, where > the columns are the rows, and vice versa? > Never mind, I discovered "Paste Special" and "Transpose". ...

transpose 3d cells to a column in single workbook
I would like to extract 3d cells from a set of worksheets into a single column on another worksheet within the same workbook. How can I do this? Insert a worksheet named Summary, then select the 3D range, and run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySht As Worksheet Dim dataSht As Worksheet Dim myRange As Range Dim myCell As Range Set dataSht = Worksheets("Summary") Set myRange = Selection For Each mySht In ActiveWindow.SelectedSheets For Each myCell In mySht.Range(myRange.Address) dataSht.Range("A65536").End(xlUp)(2).Value = _ myCell.Value N...

OutLook2000 all empty
Hi, Recently while installing some software on my computer, upon completion when i went to Outlook 2000, it was bare empty. All my emails, address, folders. I looked here adn tried to look for the dbt file ut search showed none, and the dbx file was the new one which is empty. Any one able to give me help and advise as to how and what i do to recover back all my emails, folders and address contacts ? thanks and regards, johnny What did you install? Your post is confusing. You mention using Outlook 2000 yet you are searching for the files used by Outlook Express. Which are you actual...

Loven test for there help
...

Add blank space to a cell if it's empty
How can this be done in VBA? Select some cells For each cell, if the cell.contents = "" ; is empty then set cell.contents = " " ; blank space that's it thanks! Putting a space character in empty cells is usually a bad idea. If you have formulas that use those cells, you may have to modify those formulas to treat those cells differently. But you could select the range you want and loop through the cells: Option Explicit Sub testme() dim myRng as range Dim myCell as range set myrng = selection for each mycell in myrng.cells if mycell.value = &q...

cursor tracks / highlights recalculating cell?
Hi - I've inherited a worksheet in which, when a row is inserted in a certain place and then data is entered into a certain cell, the cursor will highlight a few individual cells in sequence as they are recalculated. I've never known about this behavior, and I don't know how to turn it on / off. Can someone please help! Thanks! I can add more info, now. The formulas that end up appearing to change themselves are =SUM formulas. So - I insert a row at the bottom of the list, right above the line that has sums, counts, etc. I realize that this is normally bad form, because the...

Printing Checks to Generic Printer / Test File
I am trying to convert to Money from Quicken. I use special software to printer my checks. I Quicken I could print my checks to a generic Test file. I cannot select that output file from the printer options available from the Money Print Checks Menu. Can someone help me get an output file setup up as a printer option. This issue may be the show stopper for my using Money Thanks in advance You'll have to setup a printer queue that prints to a file and then setup Money (File|Print Setup|Check Printing, I think is the menu choice) to use this printer for printing your checks. Setting ...

Extra White Space between Cells #2
I have several lines of text in two merged cells one above the other and it appears to be spaced correctly, but when I print (or even print preview) there is an extra line space in the document. When I adjust it to print correctly it doesn't all appear on the screen. What am I doing wrong? I need both view and print. Thanks for any help. this was a duplicate** ...

How to convert numbered cells into blank
Hi I would like to know how do i convert a blank cell to a 'completely' blank cell. The blank cell is generated under the IF logic (under the " "); but though we see in as a blank cell it is not completely blank. I need it to be completely blank so I can plot the graph as non continous, rather than registering as zero. Thanks for your help in advance. Maybe this: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=209 About 2/3 of the way down the page, look for Gaps in Chart Series; Blanks Chart as Zero -- Ryan--- If this information was helpful...

Empty imported contacts
Hi I read Microsofts manual for importing contacts into Outlook from Hotmail. When I import the contacts I get 200 empty contacts. I import as semicolon separated values. I dont see the comm separated values. Anyone who can help me? I am running Windows 7. Thanks so much! Erik You provided no information with your post. Exactly what instructions did you read? We have no way of knowing what you tried. If you wanted to import a CSV file, you would first need to create such a file. Did you? What iteration of "Hotmail" are you using and how are you connecting to it? What ...

Can I create a typing test in Microsoft Word?
Is there a way that I can create a typing test in Word that measures accuracy and words per minute? There may be some online tests so that you don't have to do anything but type. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Josh Reinders" <Josh Reinders@discussions.microsoft.com> wrote in message news:E6F09B47-8285-4046-99CF-D35B7D050F3D@microsoft.com... > Is there a way that I can create a typing test in Word that measures > accuracy > and words per minute? ...