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$14=H2))-SUMPRODUCT(--($A$1:$A$14=G2),--($B$1:$B$14I2))

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 & 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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=506520

David,

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

Nikki

Whoops, 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

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=506520