Counting items based on criteria located in multiple columns

[nabok]

Hi

I'm working on a dataset which contains 'Date' and 'Type' of audits.

I need to count across the range, for number of instances where:
1. The 'Date is between 1st Jan 2018 and 1st April 2018' and
2. Type is 'S'.

There may be situations where I need to insert column between D&Q. SO, the formula should automatically adjust for the column insertion.

1.png

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[gaffney]

Hi Nabok,

Did you ever get this sorted?

Andrew

[nabok]

Hey Andrew

Not yet.

The only solution that I can think of is COUNTIFS but the problem is that it will not work across full range of cell. COUNTIF works with specified columns and not with range

[AliGW]

I can solve it for you - but I’m waiting for the workbook to be attached.

[nabok]

I have attached AliGW

[AliGW]

Where should the count appear? Where have you mocked up your expected results for the sample dataset?

[nabok]

The results should appear in Sheet 'Datamining'; Row G

[AliGW]

Yuo seem to be almost there. Try this:

=COUNTIFS('Database and Planner'!E4:AG6,"S",'Database and Planner'!D4:AF6,">="&Datamining!B4,'Database and Planner'!D4:AF6,"<="&Datamining!C4)

[nabok]

Awesome AliGW!! It works well.
Wonder where was i going wrong!!

[AliGW]

You did two things wrong:

1. Your ranges were not long (wide) enough.
2. You were referencing the dates with the VALUE() function - that's not needed.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.