Count If with AND and Multiple OR conditions

AW2020

New Member
Joined
Oct 20, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Need some assistance with a formula.

Data (Tab 1)

AB
1Date (DD/MM/YY)Status
211/09/20Received
301/10/20Reviewed
415/10/20In Progress
516/10/20Requested
610/10/20Received

Report (Tab 2)
AB
1MonthNumber Active
2Oct-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
 

Some videos you may like

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
Joined
Apr 28, 2004
Messages
75,939
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2004
Messages
75,939
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,204
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

Book1
ABC
11Date (DD/MM/YY)Status
2211/09/2020Received
3301/10/2020Reviewed
4415/10/2020In Progress
5516/10/2020Requested
6610/10/2020Received
7
8
9
101MonthNumber Active
112Oct-203
Sheet1
Cell Formulas
RangeFormula
C11C11=SUMPRODUCT(--(MONTH(B11)=MONTH(B2:B6))*(C2:C6={"Received","In Progress","Requested"}))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Another option
Help.xlsx
AB
1Data (DD/MM/YY)Status
211/09/2020Received
301/10/2020Reviewed
415/10/2020In Progress
516/10/2019Requested
610/10/2020Received
7
Sheet1


Help.xlsx
AB
1MonthNumber Active
2Oct-202
3
Sheet2
Cell Formulas
RangeFormula
B2B2=SUM(COUNTIFS(Sheet1!A:A,">="&A2,Sheet1!A:A,"<="&EOMONTH(A2,0),Sheet1!B:B,{"Received","In progress","Requested"}))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,510
Messages
5,548,488
Members
410,840
Latest member
Kar3ousse
Top