CountIf with Date range

Paul Newcome

@Linda_T_D If you are still getting the same error then you need to make sure the first cross sheet reference is also created properly.

Linda_T_D

@Paul Newcome If you're not tired of me yet... :)

I have used the same method for the cross-sheet reference in both of these instances. The first formula returns #INVALID REF and the second returns the number I want, but will not adjust to the dates, obviously, which is what I need. Any other advice on this?

Paul Newcome

@Linda_T_D That particular error comes from having a cross sheet reference not properly set up. If you click into the cell as if you are going to edit the formula and then drop the cursor inside of the {Run Date} range, what is in the little formula helper box? Is it "Reference Another Sheet" or "Edit Reference"?

Linda_T_D

@Paul Newcome Thank you for holding my hand through this!

It said "Reference Another Sheet"

So I linked the "Run Date" column from the source sheet.

I set up the source sheet to return "3" but now I'm getting a "0" result.

Paul Newcome

@Linda_T_D Ok. What happens if you remove the first range and criteria so that it is only a COUNTIFS for the date range?

Linda_T_D

@Paul Newcome Like this? Still a "0"

Paul Newcome

@Linda_T_D Ok. SO we have a problem with the dates. Did you ensure that all three are set as date type columns? If so, exactly how are each of the three being populated?

Linda_T_D

@Paul Newcome Thank you so much for hanging in there with me on this!

The "Start Date" and "End Date" columns are being populated by links to another sheet, and you're correct, they were not set up as date-type columns. This seems to have solved the issue! I'm learning as I go and this was a huge help.

Thank you again!!

Linda_T_D

@Paul Newcome Me again! 😬

So this seems to be counting only if there's one selection in the cell. There will always be multiple selections in the cells in this column. Can you help, please?

=COUNTIFS({Shared Content Planning_Tracking Range 1}, "Turrell Fund", {Shared Content Planning_Tracking Range 2}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))

Paul Newcome

@Linda_T_D To accommodate searching through multiple selections in a cell you would need to incorporate a HAS function like so:

=COUNTIFS({Shared Content Planning_Tracking Range 1}, HAS(@cell, "Turrell Fund"), {Shared Content Planning_Tracking Range 2}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))

Linda_T_D

@Paul Newcome Thank you so much! This is going to change my life! :)

Paul Newcome

@Linda_T_D Happy to help. 👍️

JessicaDiPietro

Hi! I have read through this thread for tips but am still getting an #unparseable error when referencing another sheet. I want to find the number of webinars we have published in 2023:

edcal data.png

=COUNTIFS({Editorial Calendar Range 2}, "webinar", {Editorial Calendar Range 3}, "completed", AND({Editorial Calendar Range 1}@cell<= DATE(2023,01,01)))

Range 1 is End Date

Range 2 is the Content type (multi-select)

Range 3 Is the Status

Paul Newcome

@JessicaDiPietro Try this:

=COUNTIFS({Editorial Calendar Range 2}, @cell = "webinar", {Editorial Calendar Range 3}, @cell = "completed", {Editorial Calendar Range 1}, IFERROR(YEAR(@cell), 0) = 2023)

JessicaDiPietro

It worked, Paul! You have made my day, thank you!