Matching a Date from Origin to Destination Sheet

andyhob

I am running a countif formula to count the number of certain events each day. The origin sheet has the "Event" column and a "Date" column that are feeding the count to the destination sheet. The destination sheet had a "Date" column and individual columns that contain the formula showing the counts per day of each event. Below is the formula I'm currently using:

=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, HAS(@cell, "04/12/24"))

My question is: Is there a way to write the formula so that the formula matches the "Date" in the row of the destination sheet to the "Date" in the origin sheet so I can convert this to a column formula and not have to change the date in every cell?

Thanks in advance

Genevieve P.

Hi @andyhob

Is your  {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

Let me know if this makes sense and works for you.

Cheers,

Genevieve

Genevieve P.

Hi @andyhob

Try and INDEX(MATCH combination! 🙂

We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

=INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

=SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

Let me know if either of these work for you!

Cheers,

Genevieve

Genevieve P.

Hi @andyhob

Is your  {Event Report Form Range 4} referencing a date type of column, or a text column with dates listed as text?

If it's text, unfortunately you will need to search for the text by using quotes as you have there, and change it for each row/date.

However if your Range 4 column is a Date column then yes! You can simply reference a Date cell in your current sheet to look for that value:

=COUNTIFS({Event Report Form Range 1}, HAS(@cell, "Decision to Recover"), {Event Report Form Range 4}, [Date Reference]@row))

Let me know if this makes sense and works for you.

Cheers,

Genevieve

andyhob

Thanks, I don't why that did come to mind. It worked perfect.

andyhob

On a similar note. If I am using WEEKNUMBER(TODAY(+1)) in one sheet to keep an updated count for the week; does the column in my origin sheet need to also use this formula, or will in recognize a manually input numerical value?

Genevieve P.

I'm glad that worked for you! 🙂

You can match an output from your WEEKNUMBER function to a manually input numerical value, yes!

andyhob

Ok, well I guess I need help with that one too.

I have an origin sheet that I am trying to pull the contents of "Decision to Recover" based on the "Week"

In the destination sheet the "Week" column has formula =WEEKNUMBER(TODAY(+1)) to roll over at each new week.

I am trying to pull the value from "Decision to Recover" the corresponds to the "Week" value from the origin sheet that matches the "Week" value in the destination sheet.

I have tried multiple variations of COUNTIF formulas and get different error messages and sometimes "0" when there should be a value.

Your help is very much appreciated.

Genevieve P.

Hi @andyhob

Try and INDEX(MATCH combination! 🙂

We'll INDEX the column you want to return, using the MATCHING value of the week number. Something like this:

=INDEX({Decision to Recover Column}, MATCH(Week@row, {Week Column}, 0))

You could also likely use a SUMIFS, if you would have duplicate week rows in your source sheet:

=SUMIFS({Decision to Recover Column}, {Week Column}, Week@row)

Let me know if either of these work for you!

Cheers,

Genevieve

andyhob

Your INDEX formula worked perfectly!! Thanks again for the help!