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