Countifs dates and Reason

High77

New Member
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.

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

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
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
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
Maybe
Excel Formula:
`` =COUNTIFS(Table1[Date],">=01/01/2020",Table1[Date],"<=31/01/2020",Table1[Reason],Table1[@Reason])``

arthurbr

Well-known Member

Have a look at How to Create a Pivot Table in Excel
an at
It's quite easy once you have tried it a couple of times

High77

New Member
Have a look at How to Create a Pivot Table in Excel
an at
It's quite easy once you have tried it a couple of times
Thanks, I've used Pivot tables to get exactly what I want. It was straight forward and ground breaking for me. Thanks for your help.

Most welcome

Replies
4
Views
176
Replies
8
Views
224
Replies
14
Views
169
Replies
1
Views
101
Replies
7
Views
211