Count If with AND and Multiple OR conditions

[AW2020]

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

 

[Norie]

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.

=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]

Actually came up with something a bit more compact.

=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]

try this

Book1
	A	B	C
1	1	Date (DD/MM/YY)	Status
2	2	11/09/2020	Received
3	3	01/10/2020	Reviewed
4	4	15/10/2020	In Progress
5	5	16/10/2020	Requested
6	6	10/10/2020	Received
7
8
9
10	1	Month	Number Active
11	2	Oct-20	3
Sheet1

Cell Formulas
Range	Formula
C11	C11	=SUMPRODUCT(--(MONTH(B11)=MONTH(B2:B6))*(C2:C6={"Received","In Progress","Requested"}))

 

[Fluff]

Another option

Help.xlsx
	A	B
1	Data (DD/MM/YY)	Status
2	11/09/2020	Received
3	01/10/2020	Reviewed
4	15/10/2020	In Progress
5	16/10/2019	Requested
6	10/10/2020	Received
7
Sheet1

Help.xlsx
	A	B
1	Month	Number Active
2	Oct-20	2
3
Sheet2

Cell Formulas
Range	Formula
B2	B2	=SUM(COUNTIFS(Sheet1!A:A,">="&A2,Sheet1!A:A,"<="&EOMONTH(A2,0),Sheet1!B:B,{"Received","In progress","Requested"}))