CountIf & multiple criteria

O

Otto Moehrbach

Excel XP
The formula =CountIf(A1:A50,"<80") counts the cells that have a value less
than 80. How would I modify this formula to change the criteria to less
than 80 AND more than 50? Thanks for your time. Otto
 
B

Bernard Liengme

Number less than 80 subtract number less than 50
=CountIf(A1:A50,"<80") - CountIf(A1:A50,"<50")
You may need: Number less than 80 subtract number less than or equal to 50
=CountIf(A1:A50,"<80") - CountIf(A1:A50,"<=50")


OR use SUMPRODUCT to range <80 and range > 50
=SUMPROUDCT(--(A1:A50<80) ,--(A1:A50>50))

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
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct

best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top