Countifs dates and Reason

High77

New Member
Joined
Jul 6, 2010
Messages
13
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi,

I'm looking to count all the "Failure of Mobilising Equipment" for each month. I've made the data into a table (Table1), so it'll auto expand when new entries are added.

1605193154194.png

1605193495666.png

The reason column is also a Named list (Name Manager).

I've tried:
=COUNTIFS(Table1,">=01/01/2020",Table1,"<=31/01/2020",Table1,"=[@Reason]")

But it comes up as 0, when it should be 2. Even adding & doesn't give the correct answer. At the moment I've just got it counting for dates without the "Failure of Mobilising Equipment".

Any suggestions?

Dave
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,161
Office Version
  1. 2010
Shouldn't it be
Excel Formula:
 =COUNTIFS(Table1[@Date],">=01/01/2020",Table1[@Date],"<=31/01/2020",Table1[@Reason],A2)
A2 is the reference of your reason

OTOH a Pivot Table is much simpler
 

High77

New Member
Joined
Jul 6, 2010
Messages
13
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Shouldn't it be
Excel Formula:
 =COUNTIFS(Table1[@Date],">=01/01/2020",Table1[@Date],"<=31/01/2020",Table1[@Reason],A2)
A2 is the reference of your reason

OTOH a Pivot Table is much simpler
Thanks for the quick reply. I'm still getting 0 as a return though.

I've not used pivot tables before, without wanting too much, can you point me to somewhere that will aid me with what I want to do?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
 =COUNTIFS(Table1[Date],">=01/01/2020",Table1[Date],"<=31/01/2020",Table1[Reason],Table1[@Reason])
 

High77

New Member
Joined
Jul 6, 2010
Messages
13
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,474
Members
412,595
Latest member
slim313
Top