Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumif() help
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...o&userid=26040 View this thread: http://www.excelforum.com/showthread...hreadid=474781 |
#2
|
|||
|
|||
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. Hi jhorsley Your formula looks fine, the problem appears to be with your data and 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 not have been counted, to remove the trailling spaces in cell D2 use this formula =TRIM(A2), copy down, move cursor over bottom right corner of cell D2 it will bocome a black cross double click this will copy the formula down to the bottom of the list, then select cells D2- D1567 and copy them and paste as values into column A In your usage list some of the window types eg 5050SL appear twicw and 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 table -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=474781 |
#3
|
|||
|
|||
My guess is your list of names are NOT identical throughout your range on the 'Quantity' tab, e.g. "4040 SL" (7 characters in name, counting spaces) is NOT the same as "4040 SL " (13 characters, counting spaces) check cells: A1256, 1264, 1273 and 1281 for this anomoly. Use "=LEN(A2)" copied down, then apply your filter to find differences. HTH -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=474781 |
#4
|
|||
|
|||
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...o&userid=26040 View this thread: http://www.excelforum.com/showthread...hreadid=474781 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |