We have been able to conditionally sum a column of cells (sum_range) based on the blank cells in another column (range) using SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the NON-BLANK cells in the sum_range column. How do we create a formula that will sum the non-blank cells? Please note that we have tried several iterations of the formula 'criteria' argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), "ISBLANK()", and so forth. We have most likely failed to understand the proper syntax for the 'criteria' argument, but there surely must be a criteria that will identify the non-blank cells, isn't there? Currently, we are subtracting the sumif result, for the blank cell criteria, from the total of the column in order to find the difference. Unfortunately, this is a temporary and cumbersome work-around. We thank you for any help with this issue.

0 |

3/9/2010 6:18:17 PM

You want to compare column A to non-blanks, right??? =SUMIF(A$8:A20,"<>",B$8:B20) Blue Max wrote: > > We have been able to conditionally sum a column of cells (sum_range) based > on the blank cells in another column (range) using > SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the > NON-BLANK cells in the sum_range column. How do we create a formula that > will sum the non-blank cells? > > Please note that we have tried several iterations of the formula 'criteria' > argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), > "ISBLANK()", and so forth. We have most likely failed to understand the > proper syntax for the 'criteria' argument, but there surely must be a > criteria that will identify the non-blank cells, isn't there? Currently, we > are subtracting the sumif result, for the blank cell criteria, from the > total of the column in order to find the difference. Unfortunately, this is > a temporary and cumbersome work-around. We thank you for any help with this > issue. -- Dave Peterson

0 |

3/9/2010 6:43:18 PM

Thanks, Dave, I can't believe I overlooked such a simple solution! Your suggestion worked perfectly. However, I still would like to know why the NOT() and ISBANK() functions did not work. I also would like to know exactly why the "<>" format does work? When a value is not specified, is this criteria comparing whether the value is non-zero, non-text, or blank by default? I would like to understand better how this criteria works so that I could use it under a variety of circumstances. Thank you so much for a great solution, Richard ************ "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4B9696C6.5E247A03@verizonXSPAM.net... > You want to compare column A to non-blanks, right??? > > =SUMIF(A$8:A20,"<>",B$8:B20) > > > > Blue Max wrote: >> >> We have been able to conditionally sum a column of cells (sum_range) >> based >> on the blank cells in another column (range) using >> SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the >> NON-BLANK cells in the sum_range column. How do we create a formula that >> will sum the non-blank cells? >> >> Please note that we have tried several iterations of the formula >> 'criteria' >> argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), >> "ISBLANK()", and so forth. We have most likely failed to understand the >> proper syntax for the 'criteria' argument, but there surely must be a >> criteria that will identify the non-blank cells, isn't there? Currently, >> we >> are subtracting the sumif result, for the blank cell criteria, from the >> total of the column in order to find the difference. Unfortunately, this >> is >> a temporary and cumbersome work-around. We thank you for any help with >> this >> issue. > > -- > > Dave Peterson

-1 |

3/9/2010 7:51:56 PM

If you wanted to check to see if the range was equal to the string "asdf", you could use: =sumif(a1:a10,"asdf",b1:b10) or =sumif(a1:a10,"=asdf",b1:b10) or even =sumif(a1:a10,"="&"asdf",b1:b10) if you wanted to check the cells were not equal to "asdf", you could use: =sumif(a1:a10,"<>asdf",b1:b10) or even =sumif(a1:a10,"<>"&"asdf",b1:b10) Replacing that "asdf" with an empty string: "<>"&"" or which evaluates to simply: "<>" Blue Max wrote: > > Thanks, Dave, I can't believe I overlooked such a simple solution! Your > suggestion worked perfectly. However, I still would like to know why the > NOT() and ISBANK() functions did not work. I also would like to know > exactly why the "<>" format does work? > > When a value is not specified, is this criteria comparing whether the value > is non-zero, non-text, or blank by default? I would like to understand > better how this criteria works so that I could use it under a variety of > circumstances. > > Thank you so much for a great solution, > > Richard > > ************ > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message > news:4B9696C6.5E247A03@verizonXSPAM.net... > > You want to compare column A to non-blanks, right??? > > > > =SUMIF(A$8:A20,"<>",B$8:B20) > > > > > > > > Blue Max wrote: > >> > >> We have been able to conditionally sum a column of cells (sum_range) > >> based > >> on the blank cells in another column (range) using > >> SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the > >> NON-BLANK cells in the sum_range column. How do we create a formula that > >> will sum the non-blank cells? > >> > >> Please note that we have tried several iterations of the formula > >> 'criteria' > >> argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", ISBLANK(), > >> "ISBLANK()", and so forth. We have most likely failed to understand the > >> proper syntax for the 'criteria' argument, but there surely must be a > >> criteria that will identify the non-blank cells, isn't there? Currently, > >> we > >> are subtracting the sumif result, for the blank cell criteria, from the > >> total of the column in order to find the difference. Unfortunately, this > >> is > >> a temporary and cumbersome work-around. We thank you for any help with > >> this > >> issue. > > > > -- > > > > Dave Peterson -- Dave Peterson

-1 |

3/9/2010 10:09:01 PM

Thank your for the additional information, Dave. If I understand correctly then, the "<>" logical operator literally represents 'Not Equal', but also infers the values are being compared to a null string when a specific value is not specified (hence "<>" is the same as "<>"&""). With that, I think I understand. Thanks again, Richard ********************** "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4B96C6FD.DA094559@verizonXSPAM.net... > If you wanted to check to see if the range was equal to the string "asdf", > you > could use: > > =sumif(a1:a10,"asdf",b1:b10) > or > =sumif(a1:a10,"=asdf",b1:b10) > or even > =sumif(a1:a10,"="&"asdf",b1:b10) > > if you wanted to check the cells were not equal to "asdf", you could use: > > =sumif(a1:a10,"<>asdf",b1:b10) > or even > =sumif(a1:a10,"<>"&"asdf",b1:b10) > > Replacing that "asdf" with an empty string: > "<>"&"" > or > which evaluates to simply: > "<>" > > > > > > Blue Max wrote: >> >> Thanks, Dave, I can't believe I overlooked such a simple solution! Your >> suggestion worked perfectly. However, I still would like to know why the >> NOT() and ISBANK() functions did not work. I also would like to know >> exactly why the "<>" format does work? >> >> When a value is not specified, is this criteria comparing whether the >> value >> is non-zero, non-text, or blank by default? I would like to understand >> better how this criteria works so that I could use it under a variety of >> circumstances. >> >> Thank you so much for a great solution, >> >> Richard >> >> ************ >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message >> news:4B9696C6.5E247A03@verizonXSPAM.net... >> > You want to compare column A to non-blanks, right??? >> > >> > =SUMIF(A$8:A20,"<>",B$8:B20) >> > >> > >> > >> > Blue Max wrote: >> >> >> >> We have been able to conditionally sum a column of cells (sum_range) >> >> based >> >> on the blank cells in another column (range) using >> >> SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the >> >> NON-BLANK cells in the sum_range column. How do we create a formula >> >> that >> >> will sum the non-blank cells? >> >> >> >> Please note that we have tried several iterations of the formula >> >> 'criteria' >> >> argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", >> >> ISBLANK(), >> >> "ISBLANK()", and so forth. We have most likely failed to understand >> >> the >> >> proper syntax for the 'criteria' argument, but there surely must be a >> >> criteria that will identify the non-blank cells, isn't there? >> >> Currently, >> >> we >> >> are subtracting the sumif result, for the blank cell criteria, from >> >> the >> >> total of the column in order to find the difference. Unfortunately, >> >> this >> >> is >> >> a temporary and cumbersome work-around. We thank you for any help >> >> with >> >> this >> >> issue. >> > >> > -- >> > >> > Dave Peterson > > -- > > Dave Peterson

0 |

3/9/2010 11:49:21 PM

I think you've got it <vbg>. Blue Max wrote: > > Thank your for the additional information, Dave. If I understand correctly > then, the "<>" logical operator literally represents 'Not Equal', but also > infers the values are being compared to a null string when a specific value > is not specified (hence "<>" is the same as "<>"&""). With that, I think I > understand. > > Thanks again, > > Richard > > ********************** > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message > news:4B96C6FD.DA094559@verizonXSPAM.net... > > If you wanted to check to see if the range was equal to the string "asdf", > > you > > could use: > > > > =sumif(a1:a10,"asdf",b1:b10) > > or > > =sumif(a1:a10,"=asdf",b1:b10) > > or even > > =sumif(a1:a10,"="&"asdf",b1:b10) > > > > if you wanted to check the cells were not equal to "asdf", you could use: > > > > =sumif(a1:a10,"<>asdf",b1:b10) > > or even > > =sumif(a1:a10,"<>"&"asdf",b1:b10) > > > > Replacing that "asdf" with an empty string: > > "<>"&"" > > or > > which evaluates to simply: > > "<>" > > > > > > > > > > > > Blue Max wrote: > >> > >> Thanks, Dave, I can't believe I overlooked such a simple solution! Your > >> suggestion worked perfectly. However, I still would like to know why the > >> NOT() and ISBANK() functions did not work. I also would like to know > >> exactly why the "<>" format does work? > >> > >> When a value is not specified, is this criteria comparing whether the > >> value > >> is non-zero, non-text, or blank by default? I would like to understand > >> better how this criteria works so that I could use it under a variety of > >> circumstances. > >> > >> Thank you so much for a great solution, > >> > >> Richard > >> > >> ************ > >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message > >> news:4B9696C6.5E247A03@verizonXSPAM.net... > >> > You want to compare column A to non-blanks, right??? > >> > > >> > =SUMIF(A$8:A20,"<>",B$8:B20) > >> > > >> > > >> > > >> > Blue Max wrote: > >> >> > >> >> We have been able to conditionally sum a column of cells (sum_range) > >> >> based > >> >> on the blank cells in another column (range) using > >> >> SUMIF(A$8:A20,"",B$8:B20). Nevertheless, we actually need to sum the > >> >> NON-BLANK cells in the sum_range column. How do we create a formula > >> >> that > >> >> will sum the non-blank cells? > >> >> > >> >> Please note that we have tried several iterations of the formula > >> >> 'criteria' > >> >> argument including NOT(""), NOT(""""), "NOT("")", "NOT("""")", > >> >> ISBLANK(), > >> >> "ISBLANK()", and so forth. We have most likely failed to understand > >> >> the > >> >> proper syntax for the 'criteria' argument, but there surely must be a > >> >> criteria that will identify the non-blank cells, isn't there? > >> >> Currently, > >> >> we > >> >> are subtracting the sumif result, for the blank cell criteria, from > >> >> the > >> >> total of the column in order to find the difference. Unfortunately, > >> >> this > >> >> is > >> >> a temporary and cumbersome work-around. We thank you for any help > >> >> with > >> >> this > >> >> issue. > >> > > >> > -- > >> > > >> > Dave Peterson > > > > -- > > > > Dave Peterson -- Dave Peterson

0 |

3/10/2010 1:40:28 AM

At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

When I am filling in a cell I will often use the "@" symbol, for email addresses or directions etc. I would like to turn this feature off but haven't been able to find a way to do so. Unchecking the autocomplete for cell values option in the Advanced Excel Options doesn't do the trick. Excel Options, Proofing, Check Ignore internet and file addresses Kevin U wrote: > When I am filling in a cell I will often use the "@" symbol, for email > addresses or directions etc. I would like to turn this feature off but > haven't been able to f...

When I print an excel worksheet, I have line lines running through th some of the numbers. It looks like I put a number in and then crosse it out. I have gone into format cells and cleared everything but the line still appear -- Message posted from http://www.ExcelForum.com Move the cursor slowly across the line, and if it changes into a different kind of cross with arrowheads on the ends, then the line is probably a drawing object...........while the cursor is in that new shape, just Right-click > Cut......... Otherwise, the cell might be being formatted by a ChangeEvent macro..........

Hi all, Is there a way to do this in excel? I have a column of numbers and I would like to count what the max number of consecutive negatives are. For instance, if I have a series of numbers (1,3,5,-2,-5,3,-7)), I would like it to return 2 as the max number of consecutive negatives. Thanks in advance! --- Message posted from http://www.ExcelForum.com/ There may be a more elegant way, but here is one that worked for me: Assuming the data resides in Column A (I will use A2:A10 for illustration), I copy the following formula down next to the data in Column B from B2 to B10: =IF(A2<0,...

In Excel, I want to have a spreadsheet where users can check-mark cells, just by clicking that cell...not actually having to type an X in it. Is this possible and if so, how? jjakel, you can with some code, put in worksheet code, right click on the worksheet tab a view code, paste this in, will put an X in column A when you click in it, will also remove it if you click it again Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Count > 1 Then Exit Sub If Target.Column = 1 Then If Len(Trim(Target.Value)) = 0 Then Target.Value = "X" Else Target...

I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

When I use the superscript option after selecting just a portion of the cell, the superscript appears during edit mode only. How do I get it so it appears as superscript on the spreadsheet or when printing? Again, I'm only trying to superscript part of a cell's contents, not the entire cell. ...

I have a PopUp Form that works fine - - except if the query which is its record source returns (has returned) no record. In this case the form opens to a blank white - not even labels show. Have never seen this before and would welcome advice that explains why even labels do not appear. Related to this; in the remote situation where there is no record I probably need a default message in this popup form and was initially thinking a non-visible label made visible if a field was null . But in seeing the entire form blank when there is no record (which I suppose is a state different t...

Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

How do I get a cell to offer options in Excel 97. For example I want t be able to chose by clicking on the cell between these options: $92.40 $67.30 Thank yo -- Message posted from http://www.ExcelForum.com Hi Ram1000! You can use: Data > Validation > List Type in your options with the separator used in functions (comma or continental Europe is ;) Or probably better, use an IF function if you can determine the criteria for determining the option. Example: =IF(A1<1000,92.4,67.3) In both cases it is better to use cell references than to hard code the amounts. -- Regards ...

How do I prevent users of my workbook from making a cell blank? I want them to be able to edit it so have left it unprotected but I donot want them to be able to leave it blank. It looks as if the Data Validation command should do it, but I can't get it to work. ...

I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

I am using msoffice 2003 and winxp pro We have a master workbook that contains worksheets for all vendors we do business with, each vendor having it's own worksheet in the workbook. We have workbooks for each of our customers, each workbook containing a worksheet for each vendor the customer buys products from. The master workbook contains the pricing for all products. When prices change, we are currently making those changes on the customer worksheets manually. I want to make this automatic by linking the customer worksheet to the appropriate vendor worksheet in the master workbook so...

I have a cell containing data separate with a comma in unsorted order. I need to sorted in order. The data is within a cell such as A2. Example: F1, F5, F2, F14, F6, F8; need to sort in order of F1, F2, F5, F6, F8, F14. Any help would be appreciated. see if this helps http://tinyurl.com/4ugl3 -- Don Guillett SalesAid Software donaldb@281.com "Kyle" <anonymous@discussions.microsoft.com> wrote in message news:2bf701c4be5b$1c3b5b90$a301280a@phx.gbl... > I have a cell containing data separate with a comma in > unsorted order. I need to sorted in order. The data is &...

I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

I want to be alerted if more than 255 characters are inserted into a cell. Thank you for any help. Pat Highlight applicable cell(s); At the menu - Data, Validation, Allow (Select Text Length) Enter Minimum 0, Max 255 Done "Pat" <glass_patrick@hotmail.com> wrote in message news:cje47k$6qo$1@newsg4.svr.pol.co.uk... > I want to be alerted if more than 255 characters are inserted into a cell. > Thank you for any help. > > Pat > > Data Validation - Hi Pat, With the cell highlighted, click Validation under the Data menu. Unde the Validation Criteria sele...

Hi, I have a simple spreadsheet with column B containing all dates, and column D containing all numerical values. The dates range throughout a year. At the bottom of the sheet I've entered January through December in another column. To the immediate right of the month named, I have a formula which gives me the total for that month. For instance, next to January my formula reads: =sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems to me I should be able to use SUMIF too. I've tried =sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank ce...

I'm tring to get excel to do a conditional sum based on the following. I have two columns, the first containing a reference number, and th second an ammount which is either positive or negative. In another sheet I have the reference number and beside it i want cell to calculte the sum of all the sells with that ref number and tha contain a positive amount, see the example below. Ref # Ammount 1 -5.15 1 6.00 1 -3,50 2 2.20 2 -3.40 2 2.40 3 6.40 3 -7.20 3 -1.80 SO then on another sheet i have Ref Positive Total Negativ...

I want to add those cells in column C of which the corresponding cells in column A have a certain Interior.PatternColorIndex (horizontal pattern). If anyone provides me with the necessary code I will be thankful. -- Jack Sons The Netherlands ...

When I view the Queues Tab under Internet Mail Service I see Emails from users who are not employed by my company within the Outbound Messages Awaiting Delivery section. The Destination address is shown but the Originator address only shows the <> signs. check to make sure you are not running an open relay....spammers love them. http://www.msexchange.org/pages/article_p.asp?id=54 "Bill Jones" <anonymous@discussions.microsoft.com> wrote in message news:0e1b01c5349c$8a0b0e10$a501280a@phx.gbl... > When I view the Queues Tab under Internet Mail Service I > se...

I have created a dropdown box which allows me to choose from the list of tabs -- each tab represents a month. I want to use that drop down selection in a vlookup....=MAX(IF('Aug 2009'!G45:G143=G9,'Aug 2009'! B45:B143,FALSE)) How can I replace the 'Aug 2009' so that the formula uses the month/tab selected from the dropdown menu? Thanks! Red Dianthus wrote: > I have created a dropdown box which allows me to choose from the list > of tabs -- each tab represents a month. I want to use that drop down > selection in a vlookup....=MAX(IF('Aug 2009'!G45:G1...

Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Toolbars for "Standard" and "Formatting" do not appear on a blank new document, even though they are checked off under "View" in the Menu Bar. To make them appear you have to uncheck them and then check them and then go to "Window" on the Menu bar and click on zoom for them to appear. And then they disappear after being saved. <br><br>How do I get them to open up on a blank new document? You have your toolbar area (Drawer) 'Minimized' � click ...

Hi, I would like to use the SUMIF statement for summarizing when the criteria is a range and not a fixed value. Example: I have entered several invoices in column A, Arrival date in column B. On a different sheet I would like to summarize the invoices that have invoicedate between the first of and the last date of the month. Can this be done with SUMIF or is there a another way to fix this? Best regards Fredde Pretty simple. Did you try before asking? =SUMIF(A:A,G15,C:C) -- Don Guillett SalesAid Software donaldb@281.com "Fredde" <oleander@rocketmail.com> wrote in messa...

I have an excel workseet with the following type of data: white-jungle.XL white-jungle.XXL jungle-smoke.S berny blue-smoke.S berny blue-smoke.M I need to parse the 'sizes'(everything after the period) and put it another column. Any help would be appreciated. You could use Data|Text to columns Delimited by a . If there are no other dots in the cell. Randy wrote: > > I have an excel workseet with the following type of data: > white-jungle.XL > white-jungle.XXL > jungle-smoke.S > berny blue-smoke.S > berny blue...

For pointer and non-pointer initialization of an object like MyCar mycar; MyCar* mycar = new MyCar(); I heard from other people saying if object i create must live outside scape, then I use pointer version, else if only use object for a limited scope, then use non-pointer version. Does limited scope means the object is only used in the same function like: void myfunction(){ MyCar mycar; // mycar is only used inside this function } and if mycar is used by outside scope means: void myfunction(){ MyCar* mycar = new MyCar(); // mycar is used by other function also after function returns ...