Countifs with special requirement

Exxcellent

Capture 1.PNG

Hi,

I need to use countifs to count the number of calls made to customers today.

In my example, Touch 1 and Touch 2 are calls today, however I also need some filtering with user input.

The criteria are as follow:

1. Date equals to "Free Text Date" of 20/04/2020 (X1)
2. Salesman equals to "Free Text Salesman" Peter (X2)
3. Only Touch 1 and Touch 2 are considered as calls

I was trying =COUNTIFS(Stage:Stage, ("Touch 1" OR "Touch 2"), Salesman:Salesman ("X2"), Date:Date ("X1"))

X1 and X2 should be freely populated by users of the sheet without changing the code

Paul Newcome

I assume they are putting the date you are referencing in the [Report2] column?

Exxcellent

Hi Paul,

That's right

Paul Newcome

Is that a text/number type column or a date type column?

Exxcellent

Its a text/number, the column is not specified to a date type

Paul Newcome

Ok. Try something like this...

=COUNTIFS(Stage:Stage, OR(@cell = "Touch 1", @cell = "Touch 2"), Salesman:Salesman, [Report2]2, Date:Date, @cell = DATE(VALUE("20" + RIGHT([Report2]1, 2)), VALUE(MID([Report2]1, 4, 2)), VALUE(LEFT([Report2]1, 2))))