Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi Criteria Countif
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi Criteria Countif
Hi,
=SUMPRODUCT((A1:A10="Open")*(ISNUMBER(--(RIGHT(B1:B10,FIND("-",B1:B1)+1)+0<1000)))) Mike "hokiebird2008" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi Criteria Countif
hokiebird2008 wrote:
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? =SUMPRODUCT((A1:A10="Open")* (--SUBSTITUTE(0&B1:B10,"-","")=10000)* (--SUBSTITUTE(0&B1:B10,"-","")<=10999)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi Criteria Countif
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)) -- Biff Microsoft Excel MVP "hokiebird2008" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif in multi sheet | Excel Discussion (Misc queries) | |||
if formula - multi criteria | Excel Worksheet Functions | |||
Lookup multi criteria | Excel Worksheet Functions | |||
Multi-conditions with SUMPRODUCT and COUNTIF | Excel Worksheet Functions | |||
Multi Criteria then sum | Excel Worksheet Functions |