software

Hi all,

Looking through my reference book of 'Excel 2000 Formulas', I am able
to find what seems to be close to what I'm looking for, but not quite.
A little background, I have many rows of the same type of information,
with different columns. One column contains numbers from 0.19 to 1.5,
another has eight different abbreviations (GE, TOS, etc).

My goal: To obtain the number of instances where the text coordinates
with the row of a certain range of numbers(ex: 0.1 - 0.4 and 0.5 - 1.0,
etc).

GE with a range of 0.1 - 0.4
GE in the range of 0.5 - 1.0
TOS with a range of 0.1 - 0.4
etc, etc

I know I have to use a COUNT function, as well as maybe a LOOKUP
function, but am having a brain lapse at how to put it all together
with a range of numbers.

Thanks in advance,
Nikki
Nikki,

=SUMPRODUCT(--($A$1:$A$14=G2),--($B$1:$B$14gt;=H2))-SUMPRODUCT(--($A$1:$A$14=G2),--($B$1:$B$14gt;I2))

Where your abbreviations are in A and values in B. I put a list of the
abbreviations in G and their corresponding value ranges in H amp; I so in
this case,G2 = GE, H2 = .1 and I2=.4. I am taking the total count of
values that are greater than or equal to .1 and subtracting the total
count that is greater than .4 from it.Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=506520David,

Yes this helped, Thanks much.
I am wondering, however, why you used quot;--quot; througout the formula.

NikkiWhoops, Sorry, Don't know where I got David...
Steve.
Apologies.
Nikki,

It is called the double unary operator. It coerces excel to treat
TRUE/FALSE arrays to 1/0 arrays. 1 being if the condition is TRUE and
0 if the condition is FALSE. That way, if all the conditions are TRUE
the result of the SUMPRODUCT for that row will be 1 and 0 if FALSE so
when it sums it is only counting 1 for each instance that all
conditions are met. This link can explain it more and other uses of
the formula.

Don't worry about the name thing!

HTH

Steve

www.xldynamic.com/source/xld.SUMPRODUCT.html--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=506520