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