CountIf & multiple criteria

[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

 

[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