Countif between 2 numbers and if another column is blank

[raehippychick]

Hi All

I am trying to do some sort of COUNTIF/SUMPRODUCT but getting more and more confused!

I keep almost getting there with SUMPRODUCT but not quite

I am looking to:

In cell CZ1 - Count instances in column BK >14 and only if there is nothing in the corresponding cell in BZ

In cell DB1 - Count instances in column BK 5 -14 inclusive and only if there is nothing in the corresponding cell in BZ

In cell DD1 - Count instances in column BK 0-4 inclusive (but not less than zero) and only if there is nothing in the corresponding cell in BZ


(Attached is a sanitised spreadsheet)

Very many thanks in advance

Rae

[DonkeyOte]

Perhaps you want:

=SUMPRODUCT(--($BK$2:$BK$64>14),--($BZ$2:$BZ$64=""))

=SUMPRODUCT(--($BK$2:$BK$64>=5),--($BK$2:$BK$64<=14),--($BZ$2:$BZ$64=""))

=SUMPRODUCT(--($BK$2:$BK$64>=0),--($BK$2:$BK$64<5),--($BZ$2:$BZ$64=""))

If working exclusively in XL2007(+) environment you can/should use COUNTIFS in pref. to SUMPRODUCT in this instance.

[Jamesr571]

Have you tried using IF with it?

EG:
First
CZ1 =IF(BZ="",COUNTIF(BK>14),"")

Second
DB1 =IF(BZ="",(COUNTIF(BK<14)-(COUNTIF(BK<5),"")

Third
DD1 =IF(BZ="",COUNTIF(BK<4),"")

[raehippychick]

Hi DonkeyOte

Huge thanks for those - the first 2 work perfectly, but the 3rd one gives me 4969 when there are only 49 instances - that's pretty much what happened when I tried (before I even got anywhere near being able to include the BZ column to it)

If I remove the = and just have >0 it works fine unless I have a zero in the list and there will always be zeores to count

I've tried it with just the >0 and adding another bit for =0 but failed dismally

[DonkeyOte]

So presumably you wish to exclude blanks in the numerical range (these will be interpreted as 0)

=SUMPRODUCT(--ISNUMBER($BK$2:$BK$64),--($BK$2:$BK$64>=0),--($BK$2:$BK$64<5),--($BZ$2:$BZ$64=""))

Of course if there are no other numerical values outside of the 5+ range (to be excluded from this calculation) you could perhaps just test the count of numbers in BK and subtract from that figure the other two results ?

[raehippychick]

Brilliant - that works perfectly

Thank you so much - and apologies for being particularly dim... should have twigged that blanks would be counted as zero

Rae