How do I count a range of cells from another columns criteria

[angiec50]

I want to count cells with text in one column that matches the criteria if
the cell show anything from 69% to 81%.

So far I have tried sumproduct, countif, sumif, I am nearly there but not
quite.

Can anyone help

 

[Bernard Liengme]

Can I rephrase this to: you want to count how many cells in column A have
text when the corresponding cell in column B is in the range 69% to 81% ?

=SUMPRODUCT(--(ISTEXT(A1:A100), --(B1:B100>=69%), --(B1:B100<=81%) )

Change the ranges (A1:A100) as needed. Unless you are using XL 2007 do not
use full column references (A:A).

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Mike H]

Maybe

=COUNTIF(A1:A31,">=69%")-COUNTIF(A1:A31,">81%")

Mike

 

[Shane Devenshire]

Hi,

If you are using Excel 2007 one formula would be:

=COUNTIFS(A1:A100,"*",B1:B100,">=69%",B1:B100,"<=81%" )

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[angiec50]

Dear Bernard. Yes your rephrase is correct however, I have typed in the
formula exactly as you have written it but when I press Enter is states that
there is an error, but wont tell me what part is errored. I have had a look
at the link you attached about SumProduct, however, that has completly
confused me. I am using Excel 2000 could this be the reason it doesnt work>

Regards

Angie

 

[David Biddulph]

Count the opening & closing parentheses. They need to match.
I think that Bernard intended to say:
=SUMPRODUCT(--ISTEXT(A1:A100), --(B1:B100>=69%), --(B1:B100<=81%))

 

[David Biddulph]

Trying to read the question you asked in the body of the message in
conjunction with the rather different question you asked in the subject
line, then perhaps:

=SUMPRODUCT((A1:A10>=69%)*(A1:A10<=81%)*ISTEXT(B1:B10)) or
=SUMPRODUCT(--(A1:A10>=69%),--(A1:A10<=81%),--ISTEXT(B1:B10))

I assume that the 69% and 81% limits are inclusive? If not, change the >=
to > and the <= to <