Sumif() help #2

Gurus,

I'm stuck with this formula.  I have a spreadsheet with two tabs.  On
one is a list of Standard Window Types in different houses and the
quantity of that type of window for that house.  The Window type can be
repeated many times with different quantities.  

On my second tab I have a list of all the unique windows listed only
once.  I want to know the frequency that a window is used in all these
projects.  I'm using
=SUMIF(Quantity!$A$2:$A$1567,Usage!A2,Quantity!$B$2:$B$1567).  The
funny thing is I've got a total of 3,213 windows in these projects but
the sum if returns a sum of 3302 (obviously something is wrong.

To add to my confusion when I sort the usage results with the most used
window on top they don't sort in the right way.  

I've attached a file showing the problem.  The sum of the qty columns
is at the bottom.

Thanks for you help.


+-------------------------------------------------------------------+
|Filename: Formula Problem.zip                                      |
|Download: http://www.excelforum.com/attachment.php?postid=3898     |
+-------------------------------------------------------------------+

-- 
jhorsley
------------------------------------------------------------------------
jhorsley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26040
View this thread: http://www.excelforum.com/showthread.php?threadid=474781

0
10/10/2005 3:28:32 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
786 Views

Similar Articles

[PageSpeed] 24

jhorsley Wrote:
> Gurus,
> 
> I'm stuck with this formula.  I have a spreadsheet with two tabs.  O
> one is a list of Standard Window Types in different houses and th
> quantity of that type of window for that house.  The Window type can b
> repeated many times with different quantities.  
> 
> On my second tab I have a list of all the unique windows listed onl
> once.  I want to know the frequency that a window is used in all thes
> projects.  I'm usin
> =SUMIF(Quantity!$A$2:$A$1567,Usage!A2,Quantity!$B$2:$B$1567).  Th
> funny thing is I've got a total of 3,213 windows in these projects bu
> the sum if returns a sum of 3302 (obviously something is wrong.
> 
> To add to my confusion when I sort the usage results with the most use
> window on top they don't sort in the right way.  
> 
> I've attached a file showing the problem.  The sum of the qty column
> is at the bottom.
> 
> Thanks for you help.

Hi jhorsley

Your formula looks fine, the problem appears to be with your data an
your usage list

In your data for example you have 5050SL, which also appears as 5050SL
where star is a space, so any items with a space at the end will no
have been counted, to remove the trailling spaces in cell D2 use thi
formula > =TRIM(A2), copy down, move cursor over bottom right corner o
cell D2 it will bocome a black cross double click this will copy th
formula down to the bottom of the list, then select cells D2- D1567 an
copy them and paste as values into column A

In your usage list some of the window types eg 5050SL appear twicw an
are therefore being double counted

Ths should sort your problem

An easier way to get the resulkts you want may be to use a pivot tabl

--
Paul Sheppar

-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=47478

0
10/10/2005 4:18:08 PM
My guess is your list of names are NOT identical throughout your rang
on the 'Quantity' tab, e.g.  "4040 SL" (7 characters in name, countin
spaces) is NOT the same as "4040 SL       " (13 characters, countin
spaces) check cells: A1256, 1264, 1273 and 1281 for this anomoly.  Us
"=LEN(A2)" copied down, then apply your filter to find differences.

HT

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=47478

0
10/10/2005 4:25:53 PM
I connect to the newsgroups directly, so I don't even see the attachment.

But I'd bet that there are text cells in that quantity!b2:b1567 range that look
like numbers--but are really text.

I'd double check that range of cells to see if all the cells are really numbers.

If you put:
=count(b2:b1567)
in a cell
and 
=counta(b2:b1567)
in a different cell

do you get different results?


If you do, select an empty cell
edit|Copy
select B2:B1567
edit|paste special|check Add

If you copied that data from a web page, you may have non-breaking spaces (HTML
stuff) in those cells.

You may want to try David McRitchie's routine to clean that stuff up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jhorsley wrote:
> 
> Gurus,
> 
> I'm stuck with this formula.  I have a spreadsheet with two tabs.  On
> one is a list of Standard Window Types in different houses and the
> quantity of that type of window for that house.  The Window type can be
> repeated many times with different quantities.
> 
> On my second tab I have a list of all the unique windows listed only
> once.  I want to know the frequency that a window is used in all these
> projects.  I'm using
> =SUMIF(Quantity!$A$2:$A$1567,Usage!A2,Quantity!$B$2:$B$1567).  The
> funny thing is I've got a total of 3,213 windows in these projects but
> the sum if returns a sum of 3302 (obviously something is wrong.
> 
> To add to my confusion when I sort the usage results with the most used
> window on top they don't sort in the right way.
> 
> I've attached a file showing the problem.  The sum of the qty columns
> is at the bottom.
> 
> Thanks for you help.
> 
> +-------------------------------------------------------------------+
> |Filename: Formula Problem.zip                                      |
> |Download: http://www.excelforum.com/attachment.php?postid=3898     |
> +-------------------------------------------------------------------+
> 
> --
> jhorsley
> ------------------------------------------------------------------------
> jhorsley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26040
> View this thread: http://www.excelforum.com/showthread.php?threadid=474781

-- 

Dave Peterson
0
petersod (12004)
10/10/2005 4:37:00 PM
Reply:

Similar Artilces:

Help with a query 01-23-08
Hi All, I am sure there is a simple solution to this but my brain doesn't seem to be working at the moment. In my dataset one field has the value "Data Quaternity" and the other values are NULL (but could be another string) and I want to exclude any records from the dataset which have this value. I have tried to put the following expressions in the Criteria for the query. NOT "Data Quaternity" or NOT Like "Data Quaternity" But when I do this all I get is an empty data set. I can sort on IS NULL in the criteria now, but I don't want to do this as ...

help moving to new exchange server
Hi i have hopefully a quick and easy question. we are in the process of migrating our exchange 5.5 server, to exchange 2003. what i need as a quick easy way to modify everyones outlook profile to point to the new server. the new server is in a whole new org, and with a new name. we are using outlook 98,2000,xp, and 2003 i'm leaning towards using the .prf files, but what i am hoping is that someone else has done this and has some scripts already made :) Thanks for any help anyone can give. Where do you get stuck with creating a prf-file? A better way to do it is by using the foll...

error bars #2
Help! I am trying to put error bars on my data. I have a number of series in my chart (xy plot). One set has a number of points (series 1), then I plot the average of those points in a different color (series 2) on top of that data, but when I put the error bars on the series 2, they are masked by the data from series 1 even though the data point of series 2 is on top of the data from series 1. Does anyone know how I can pull the error bars to the front so they are with the associated data point? I have tried moving the order of the series, but the error bars always remain in th...

CRM 1.2 saying ASP.NET not installed
HI. I'm trying to install the CRM 1.2 on our Windows 2003 server and when the installation starts in says that ASP.NET 1.1 is not installed but it is as we have other applications running with no problems, anyone else had this problem ?? Thanks Paul Paul, Have you tried to look for ASP.NET in you Control Panel (Add orRemove Programs  Add/Remove Windows Components). In Windows Components Wizard select Application Server. Details button opens Application Server window that contains ASP.NET option. See if it is checked. >-----Original Message----- >HI. > >I&...

sumif formula #2
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...

Need Help! Changing country
I just moved from Canada to China. In Canada, when each new contact popup, the default long distance prefix is of Canada. But after backing up the Outlook folders and now moved to China, and restore the data to a new system, Outlook still insists that the default country for new contact is Canada. I've set the Control Panel -> Regional and Language Options -> Location to China, but with no effect on Outlook whatsoever. Please if anyone can help... Thanks You seem to have neglected to reset the default dialing location for the operating system. ------ Russ Valentine [MVP-Out...

print preview #2
I'm sorry I did'nt mean to stump everybody with my previous post. Jim ...

Please help!! Microsoft Office Student and Teacher
My cousin and I started college this year, so we both needed Microsoft Office Student and Teacher, the 2007 version. When she bought her computer, the guy said that it had a couple activations, so that we both would be able to use it. He was wrong, since it was an OEM version, there was only one activation. The problem is I activated mine before we tried to do hers, so my Student and Teacher is activated using her number. So I was wondering if there was anyway to deactivate mine, and then activate hers using her number. ...

pivot table help!
I need some help with an issue I cannot seem to solve! I have a pivot table in Excel that is based off of a data input sheet. The data changes every month and the pivot table is always linked to the specific data tab. When the underlying data changes, the "old data" still displays as options to check in the pivot table. The refresh option does not seem to fix this, nor does the "Save data with table layout" option. I only want the options to check to be current data. For example, my data sheet only has data for July and August, but the pivot table has options to check...

Finance Charge #2
Client needs to access finance charges based on a daily rate. So for May the finance charge would be more than for June (31 vs 30 days). As far as I can see, GP just takes open balance and multiplies by whatever rate is indicated on Customer. Anyone know how to work around this or 3rd party that handles Finance Charges in the necessary manner? Ed, There is an option in Receivables to enter Finance Charges manually. Your client needs get fulfilled with this. Go to Sales=>Receivables Trx Entry=>Select Document Type as Finance Charge. They can enter whatever finance charge for t...

Macro Help
Hi All I have a workbook which contains >400 sheets. What I need to do is to insert a "cover" sheet and then create a macro to pick up the same four cells from each worksheet (B2, G9, B14 and B9) and copy and paste the values into the Cover Sheet going progressively down a column for each different sheet. so i need the macro to go to Sheet1 get the value from B2 and paste it on the cover sheet in A2, then get the value G9 and paste it on the cover sheet in B2 etc... then go to Sheet2 get the value from B2 and paste it on the cover sheet in A3, then get the value G9 and...

Help me #3
How can i change font size in combo box in exce -- raphy5 ----------------------------------------------------------------------- raphy50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1644 View this thread: http://www.excelforum.com/showthread.php?threadid=27803 Hi what combo box do you mean. The one create with 'Data - Validation'? -- Regards Frank Kabel Frankfurt, Germany raphy50 wrote: > How can i change font size in combo box in excel For controls from the Forms toolbar, the font size can't be changed. If you use controls from the Co...

Need Help in numbering cells / auto fill
Ok im having bad day got Drain Bamage In say colunm A rows 1 tru 100 I want to number these cells 1 tru 100 in column C I want to number rows 1 tru 100 as 101 thru 200 by physically typing these numbers in each cell as data. A1 is 1 A2 is 2 A99 is 99 A100 is 100 C1 is 101 C100 is 200 I know that you can use auto fill or some thing so you dont have to type each number in. You first say type in 1 2 3 and then select a range and it auto fills it.. and inserts 4 thru 100 I hope you can understand this if not let me know Hi BostonBeaner In A1 enter 1 Drag the cell down...

help..journal
Hello.. > > I am using OL 2000 (IMO)and have configured my journal to log certain > CONTACTS emails received and sent from my contact list. > > Somehow only certain contacts get listed as received and none are logged as > sent. I have placed a check next to the names I want logged.. but they are > not showing up in journal.. > > This is for emails only - not Word or Exel. > > How do I get emails to be logged for those I've chose to record?? > > help Please.. > Dave > > In Journal options, is the EMAIL option checked, as well as the me...

Help with formula trowing a #value! result
Hi I've downloaded a mortgage calculator from the web, being anewbie I'm stuck with the following issue: When the last payment comes down to 0 or near 0 like 0.03 cents all the formulas values return a #VALUE!, I want that the value be displayed as 0. In example the maximum payments are 431 monthly payments, lets say the last payment the client made was for 378, now after that paymet I need the value to be displayed as 0 up to row 431, because he has finished his payments. A formula is if(F378+D378<C378,C378-(D378+F378),"") EVALUATES TO------>...

HELP!!!!!!!!! with a Hierachial Script and XML
SQL Gurus, I need help I have a table with categories Category1==>Root SubCategory1.1 SubCategory1.2 Sub-SubCategory1.2.1 Category2==>Root SubCategory2.1 SubCategory2.2 Sub-SubCategory2.2.1 .... and so on ... Next, I have a form (HTML) with roots as checkboxes i.e Category1, Category2, Category3 1. When the user checks Category1 and clicks retrieve, return all children of Category1 2. When the user checks Category1 and Category2 and clicks retrieve, return all children of Category1 and Category2 Below is my c# script: [WebMethod] public...

need help with histogram
Hi, Is it possible to make a histogram with multiple input ranges or does anyone know what other type of distribution graph I can use if this is not possible? I have the following data (price each person sold in each day): A B C D ...so forth 3/1/06 3/2/06 3/3/06 ...daily date until end of month John .23 .15 .20 Mary .18 .10 .20 Bob .15 .15 .19 Kate .23 .16 .18 Ann .23 .15 .18 ... to row 282 I would like to know how ma...

Offline Backup Help
I am running Veritas Backup Exec 9.1 with the Exchange Server Plug-in on Exchange Server 2003. I need so help to create a offline backup. I was told the there was a how to on Veritas.com but could not find the article. Can someone point me in the right direction. The KB that I found on MS site was way more indepth than what I have heard needs to be done. Thanks I do you want to do an offline backup, this is not recommended. Performing an offline backup is simply stopping all the Exchange services and then copying the database and log files. -- Mark Fugatt "cbdrako99" &l...

sumif across multiple sheets in excel 2007 based on a condition
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...

Need help with ftp script
I'm trying to create a WinXP ftp script and I'm having a hard time even figuring out the syntax. I have an XML file on one of my websites, let's call it sourceweb.com (publicly available) that I want to copy to a directory on another of my websites, let's call it destinationweb.com, so that it overwrites the existing file. I'd like the script to include the destination's ftp user name & prompt me for the password. Can anyone please show me how to do this? Thanks!!! "Toni" <Toni@nowhere.com> wrote in message news:OOmjkD...

Excel 97 #2
I run Windows 2000. When I double click on an excel file in windows explorer, it fails to open the file. I have to go to programs, open excel and then go back and double click on the file to open it. Also my Excel email function is no longer working. Is there some upgrade that must be downloaded? sounds like you need to re-associate the excel file with the .xls file type. right click a .xls file and then choose open with... select Excel and choose always open wiht selected file type. Second issue is a bit more complex. does the send to function still work with Word or other MS Off...

VBA to Pull info from 2 diff tabs from the same spreadsheet
Hi All, I need your help please. I need to pull data from 2 different tabs from the same spreadsheet. The code down below after the first "end with" works when it is run alone, however, when I add the the code at top (to pull from the other tab) it doesn't work. i know it looks messy and crazy but would anyone know how to pull data from 2 tabs within the same spreadsheet? I'm hoping it's a simple fix. Sub HSSESafetyQuestions() Dim fso, f, fldnm As String, WB As Workbook, WS As Worksheet, r As Long Dim ws2 As Worksheet Set fso = CreateObject("Scripting.File...

Chart repeats data when printed pls help !
I have a spreadsheet that consists of a long list of data on the left with a simple x-y scatter chart on the right - both list and chart are longer than one page, and therefore must be printed across several pages. When I print the spreadsheet, the chart on each page prints the first two rows of data from the beginning of the chart, so for example if the data runs from 1-100, the first page chart printout shows 1-20, the second page shows 1-2 then 13-20, the third page shows 1-2 then 23-30. Could it be something to do with excel printing out the x axis on each page, given that the x axis is ...

SUMIF or SUMIFS
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 >...

Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...