# Count If with AND and Multiple OR conditions

#### AW2020

##### New Member
Hi all,

Need some assistance with a formula.

Data (Tab 1)

 A B 1 Date (DD/MM/YY) Status 2 11/09/20 Received 3 01/10/20 Reviewed 4 15/10/20 In Progress 5 16/10/20 Requested 6 10/10/20 Received

Report (Tab 2)
 A B 1 Month Number Active 2 Oct-20

I need a formula that counts the number of cells and puts it in Tab 2 'B2' that match the following:

1. Date (A2-A6) is within October 2020 (ie Oct-20), AND
2. Status is either Received or "In Progress" or "Requested"

So in this case the answer should be 3.

Any assistance would be greatly appreciated.

I am using O365.

Rgds,
AW 2020

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Norie

##### Well-known Member
I'm sure there are much better ways to do this but I've been playing about with FILTER with multiple criteria in Excel 365 and came up with this.
Excel Formula:
``=COUNT(FILTER(Sheet1!A1:A6,((Sheet1!B1:B6>=DATE(YEAR(B2),MONTH(B2),1))*(Sheet1!B1:B6<DATE(YEAR(B2),MONTH(B2)+1,1)))*(Sheet1!C1:C6="Received")+(Sheet1!C1:C6="In Progress")+(Sheet1!C1:C6="Requested")))``

#### Norie

##### Well-known Member
Actually came up with something a bit more compact.
Excel Formula:
``=COUNT(FILTER(Sheet1!A1:A6,((Sheet1!B1:B6>=DATE(YEAR(B2),MONTH(B2),1))*(Sheet1!B1:B6<DATE(YEAR(B2),MONTH(B2)+1,1)))*(NOT(ISERROR(MATCH(Sheet1!C1:C6,{"Received","In Progress","Requested"},0))))))``

#### AlanY

##### Well-known Member
try this

Book1
ABC
11Date (DD/MM/YY)Status
3301/10/2020Reviewed
4415/10/2020In Progress
5516/10/2020Requested
7
8
9
101MonthNumber Active
112Oct-203
Sheet1
Cell Formulas
RangeFormula

#### Fluff

##### MrExcel MVP, Moderator
Another option
Help.xlsx
AB
1Data (DD/MM/YY)Status
301/10/2020Reviewed
415/10/2020In Progress
516/10/2019Requested
7
Sheet1

Help.xlsx
AB
1MonthNumber Active
2Oct-202
3
Sheet2
Cell Formulas
RangeFormula

Replies
7
Views
74
Replies
11
Views
118
Replies
1
Views
47
Replies
5
Views
58
Replies
3
Views
92