SUMIFS using a text range

I am still new to Excel 2007. I want to start using SUMIFS for their
efficiencies over array formulas. They don=92t seem to work the same. I
want to sort by Dep and Account and by month. The data comes in TEXT
format. I can=92t get it to work without converting the TEXT to VALUES.
I bring in data and need to sort it like this.

	         Dep         AccountII        AccountII
SALARIES 	110	 60010 	 60999
 SERVICES 	110	 65000 	 65999
 MARKETING 	110	 70000 	 74999
 TRAVEL   	110	 75000 	 79999
 FACILITIES 	110	 80000 	 80109
 OTHER    	110	 80110 	 81999
 OTHER INCOME 	110	 82000 	 89999

 SALARIES 	 120 	 60000 	 60999
 SERVICES 	 120 	 65000 	 65999
 MARKETING 	 120 	 70000 	 74999
 TRAVEL   	 120 	 75000 	 79999
 FACILITIES 	 120 	80000	80109
 OTHER    	 120 	80110	81999
 OTHER INCOME 	 120 	82000	89999

This formulas works IF I convert the TEXT to VALUES. In 2003 I could
sort on the TEXT with an ARRAY formula. With 2007 this formula works
but only if I convert the TEXT to Values.

=3DSUMIFS(Amount,AccountII,">=3D"&$R10,AccountII,"<=3D"&$S10,Dep,$B10,Month=
,F
$2)

It does not work if I leave the search columns (Account) as TEXT.

Any solutions would be welcome and I thank you in advance.
0
Ziggy
3/23/2010 5:58:34 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
2108 Views

Similar Articles

[PageSpeed] 22

The AccountII columns are actually the "R" & "S" in the formula.  The
"AccountII" is the lookup range. Then there is also a Dep (Department)
lookup range and a Month range.

This allows me to sort expenses By Department, by month and in an
accounting range.

The Dep and Month are not a problem becasue the represent a fixed
value.

The problem comes because I am looking into a range of text vaues. In
other words I am looking for account values between 60000 to 60999 and
65000 to 65999, etc as TEXT. These and the lookup range is what I have
to convert to VALUES. Since they come out from the DB as text I'd
prefer to leave them as text and avoid having to convert them to
VALUES.
0
Ziggy
3/23/2010 7:02:51 PM
I have managed to get the attached workbook working with your text
data.

I have modified the first 3 columns, to all show Month1, with
differing formulae, to prove that all 3 results are the same.

Column G has your original formula, looking at extra columns where you
have converted Text to Numbers

Column H, calculates the result using Sumproduct formulae. I am not
suggesting using this as a solution, because Sumproduct is much slower
than Sumifs.

 Column F uses my revised formula

=SUMIFS(Amount,Account,">--"&$C6-1,Account,"<=--"&$D6,Dep,$B6,Month,F
$2)



This is using all of the original Text data.

I have coerced the Text to Numeric, using the double unary minus --


Roger Govier was good enough to send me this solution offline. I had
never seen the unary function before. Many thanks Roger.

It works exactly as I needed it to work.

Siegfried
0
Ziggy
3/29/2010 12:35:29 AM
Reply:

Similar Artilces:

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? Hi, =left(a1,3) "SaraMack" wrote: > I need to look up "c...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

default text height comment
Is there a way to set the default text height for a new comment? Thanks mark (I've looked through help but can't find it if it's in there.) I assume you mean the font size? There is no text height available in Excel. A comment has a shape property and that is what you can use to change the font size. They didn't make it easy ... Range("D4").Comment.Shape.TextFrame.Characters.Font.Size = 12 -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "mp" <nospam@Thanks.com> wrote in message...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Charttitle.Text crashes (Excel 2000)
Hi, when I try to run the following code (on an existing diagram): With ActiveChart Dim globalLstFile as String globalLstFile =3D "02 KOBA_XYZ_C3-06214 02- 20.7-20.8=B0C Touchprobe Mittelpunkte.lst" =2EHasTitle =3D True =2EChartTitle.Text =3D "KOBA Sphere Gauge S/N 38 538 (" + globalLstFile + ")" & Chr(10) & _ "color coding: red =3D Zone 1, green =3D Zone 2, blue =3D Zone 3"= & Chr(10) & _ "light color: measurements that contain spheres that are within 20 cm of the edge of the measurement volume" ..=...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

Outlook XP HTML email is plain text in OE6...why?
When my Father sends an HTML email to me it shows as plain text in Outlook Express6. We are both using WinXP (Him with Home and me with Pro). He is set to send HTML. His HTML message is properly formatted when received on my other computer using Outlook 98. I can receive HTML mail from others. I can receive HTML from my Outlook 98 computer that look fine. Is something wrong somewhere? Thanks Scott Do you have OE set to read all messages in plain text (Tools | Options | Read tab)? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please ...

Save as CSV isn't saving comma delimited, but is using semi-colon.
Save as CSV isn't saving comma delimited, but is using semi-colon... How do I get it to save using commas only (,)? (I'm using semicolons for other delimitations and so can't just do a global replace.) Thanks so much for your help! Hi Excel uses your Windows regional settings for saving CSV files. Also ho should an importing program of this CSV file know whether it is the list separator or one of your other separators? -- Regards Frank Kabel Frankfurt, Germany CCinItly wrote: > Save as CSV isn't saving comma delimited, but is using semi-colon... > How do I get it ...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

easy to use templates
Hi We have outlook templates that people fill in each morning and send to a distribution list. But they are not that easy to fill out as you have to place the curser in the proper spot to type then do that throughout the entire document. It is easy to overlook items. Is there a way to set up a template that you can just hit tab or enter and it automatically goes to the next area that you need to type information? -- Thank-you! Ruth What exactly are you using now? In other words, what do you mean by "templates"? Also, do you meet the prerequisites for using...