Quick Question

[Nikki]

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

 

[SteveG]

Nikki,

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

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


Does that help?

Stev

 

[Nikki]

David,

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

Nikki

 

[Nikki]

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

 

[SteveG]

Nikki,

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

Don't worry about the name thing!

HTH

Steve

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