COUNTIFS using dates and reference sheets

Lindsey B

Looking for some help with this formula. I am either getting an unparseable or a 0 when I can manually see there is a match to be counted. I'm using the following formula to try to count the instances in another sheet that match the row item, ex: name of location, if they occured before today's date. (I've set Column3 as a date column formula for TODAY - I've also tried putting TODAY directly in the formula but that was giving me more trouble)

Formula used:

=COUNTIFS({reference sheet Range 9},<[Column3]67, {reference sheet Range 1},[Reporting Topic]@row])

Totals sheet with formula:

Reference Sheet to be counted on the final sheet. Start date is listed as a date, target location is a dropdown single select.

What am I missing? Thanks!!

Paul Newcome

You still have a closing square bracket at the end that needs removed.

Paul Newcome

You need to get rid of the double closing square brackets at the end of the formula.

Lindsey B

Tried that and updated the formula shown above - still getting "unparseable"

NicoLHC

@Lindsey B , please share a copy of the sheet with me ,i can help .

Paul Newcome

Are you able to provide a screenshot of the sheet with the formula open in a cell as if you are about to edit it?

Lindsey B

In the number of targets column, this is my current formula

Paul Newcome

You still have a closing square bracket at the end that needs removed.

Lindsey B

Ah yes sorry - I'm using a dummy line and a real line in my sheet to prevent sharing all the data or names and missed updating the real line. My mistake! Thank you for your help!

After this formula change, I was still getting a 0. I finally realized I was comparing a date field and a dropdown list for my date criteria. Changing the dropdown list to a date field got me to the correct data!

NicoLHC

@Lindsey B i hope it understood correct for me its working please see….

´=COUNTIFS(Dates:Dates, <[Coloumn3]@row, Location:Location, [Reporting Topic]@row)´

Yellow is your reference Sheet , Blue you Target , orange the result ( I suggest to out names on the coloumn you are referencing otherwise it will be a mess later, Trust me ….

NicoLHC

@Lindsey B so now all resolved with my formula ?

Paul Newcome

@Lindsey B Glad you were able to get that part sorted. Yes. Data types play a big role in formulas and functions. Even if they LOOK the same, they could be stored on the back-end very differently.