CountIf with Date range

Paul Newcome

@Kendra P Happy to help. 👍️

Shadany

Hi @Paul Newcome

I'm trying to count the numbers of each causes in a month. The first part of my formula works. I have two countsifs referencing two different sheets. But now I need to just categorize it with just one month.

This is also going to a report sheet. Is there a formula that would automatically change the dates to the current month? For example for this month I want the counts for each causes. But next month I'll just need the counts for that month. I could just go in and change the date manually, but hopefully there's a function for that.

Paul Newcome

@Shadany Your range/criteria set would look something like this:

COUNTIFS({range}, criteria, {range}, AND(@cell>= DATE(yyyy, mm, dd), @cell<= DATE(yyyy, mm, dd)))

Ryan Blockston

Hello @Paul Newcome I have read through your responses to others and feel as if my quesiton is just slightly different. I am still looking for a CountIF formula in a date range but on a different sheet, so I am unsure where to put the reference to the other sheet. The formula below is the one I had attempted to alter based on what you had and it returned as #UNPARSEABLE

=COUNTIFS((KBWI Injury Tracker Range 9[01/01/2022]:[01/08/2022]), AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))

Paul Newcome

@Ryan Blockston You would create the cross sheet reference following the proper steps to reference another sheet and have it placed in the bold portion below:

=COUNTIFS({Cross Sheet Reference Goes Here}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))

Ryan Blockston

Awesome thank you so much! My formula now looks like this

=COUNTIFS({{KBWI Injury Tracker Range 8}}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022)

I also edited it so that I can track by week like this!

=COUNTIFS({KBWI Injury Tracker Range 8}, AND(IFERROR(WEEKNUMBER(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022))

Linda_T_D

Hi @Paul Newcome! I'm very new to formulas and have looked here for a solution, but I haven't found what I'm looking for yet.

I'm trying to count the occurrence of an organization name from another sheet (Shared Content Planning_Traking), between the start and end dates linked from a second sheet. The start and end dates will be updated at the end of each contract period, so I would like the count to start over once those start and end dates are updated, without having to go in and change the formula on each line. Is this something you can help with?

This is where I am now when I realized I will need the count to begin again when the contract renews. Right now there are mostly zeros because I don't have the source sheet populated yet.

=COUNTIF({Shared Content Planning_Tracking Range 2}, "Turrell Fund")

Paul Newcome

@Linda_T_D Are you able to provide some samples of how the source sheet will look?

Linda_T_D

@Paul Newcome

So the source for the "Total Videos" is the "Tail Funders" column in this sheet:

This is the source sheet for the start and end date columns:

Hope this helps. Please let me know if you need any additional info.

Paul Newcome

@Linda_T_D Are you looking to use the Run Date in the first screenshot to determine if it is between the start and end date?

Linda_T_D

@Paul Newcome Yes. I'm glad you mentioned that.

But also, when the start and end dates are changed, I would like the "Total Videos" in the first sheet to adjust to the new dates.

Thank you so much for your responsiveness.

Paul Newcome

@Linda_T_D In that case you would need a COUNTIFS to include multiple range/criteria sets then include the Run Date range and have the criteria be that it is between the start and end dates.

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

Linda_T_D

@Paul Newcome

I'm getting an #INVALID REF..?

Definitely learning here.

Paul Newcome

@Linda_T_D You will need to create the new range in the same way the first range was created.

Linda_T_D

Thank you for your patience, @Paul Newcome

I have done that for all of the rows shown – clicking on "Reference Another Sheet" and selecting the "Tail Funders" column, then updating the funder name for each row. What am I getting wrong here?