CountIfs based on if a cell in the row is >50 and a specific date

ScionoftheNight

Hi, I am working with two sheets. One sheet has active sales orders, while the other sheet is a summary sheet of the number of things that passed/failed for each day of the month (it pulls from the active sales orders sheets). For a particular day (say Sept 1st), I am trying to see how many sales orders passed. Passed means their Guide Editing Efficiency is >49.99. My formula in my summary sheet is referencing the sales order sheet and it is the following:

 =COUNTIFS({ICE Analysis - 2DP Range 3}, >49.99, {ICE Analysis - 2DP Range 4}, =DATE([Column7]@row))

^this fails, but if I do

 =COUNTIFS({ICE Analysis - 2DP Range 3}, >49.99, {ICE Analysis - 2DP Range 4}, =DATE([2019,9,1]))

I get the correct value

I don't want to have to enter in the date for every formula, I just want to be able to drag it down for any new dates. How can I do this? Thanks!

Paul Newcome

That's because you are using the DATE function. The DATE function is only needed if you are going to specify the exact date. If you are using a cell reference (as in your first formula), you do not need the DATE function since the cell is already formatted as a date.

 

Long story short... Remove the DATE function when using a cell reference that already contains a date.

 

=COUNTIFS({ICE Analysis - 2DP Range 3}, >49.99, {ICE Analysis - 2DP Range 4}, [Column7]@row)

ScionoftheNight

Thank you!

Paul Newcome

Happy to help!