Multi Criteria Countif

  • Thread starter Thread starter hokiebird2008
  • Start date Start date
H

hokiebird2008

I have a sheet with one column containing either "Open" or "Closed" and
another column with numbers ranging from "09-001" to "10-999". I need to
count the number of entries that are "Open" but only for the numbers ranging
from "10-000" to "10-999". Any suggestions?
 
Hi,

=SUMPRODUCT((A1:A10="Open")*(ISNUMBER(--(RIGHT(B1:B10,FIND("-",B1:B1)+1)+0<1000))))

Mike
 
hokiebird2008 said:
I have a sheet with one column containing either "Open" or "Closed" and
another column with numbers ranging from "09-001" to "10-999". I need to
count the number of entries that are "Open" but only for the numbers ranging
from "10-000" to "10-999". Any suggestions?
Click to expand...

=SUMPRODUCT((A1:A10="Open")*
(--SUBSTITUTE(0&B1:B10,"-","")>=10000)*
(--SUBSTITUTE(0&B1:B10,"-","")<=10999))
 
numbers ranging from "09-001" to "10-999".

Use cells to hold the criteria.

D2 = Open
E2 = 10-000
F2 = 10-999

=SUMPRODUCT(--(A2:A20=D2),--(B2:B20>=E2),--(B2:B20<=F2))
 
Back
Top