Apply Date parameter in different rows

Angela Formiga

Hello,

I need to use the same date in different rows but using a formule.

I built a sheet named "Meses" with the dates like below:

to use the same dates in different rows instead of changing by hand. However it is not working because it is returning 0 in the formule below (for example):

=SUMIFS({Project_Product Release Roadmap Intervalo 11}; {Project_Product Release Roadmap Intervalo 2}; Resource318; {Project_Product Release Roadmap Intervalo 3}; {Meses Intervalo 3}; {Project_Product Release Roadmap Intervalo 3}; {Meses Intervalo 4})

If I use the dates inside works perfectly:

=SUMIFS({Project_Product Release Roadmap Intervalo 11}; {Project_Product Release Roadmap Intervalo 2}; Recurso318; {Project_Product Release Roadmap Intervalo 3}; >=DATE(2020; 10; 1); {Project_Product Release Roadmap Intervalo 3}; <=DATE(2020; 10; 2))

Could you help me?

thank you

Genevieve P.

Hi @Angela Formiga

My apologies! I see that some of the instructions in my formula are repeated/incorrect (the <=>= is not a proper instruction). In addition, you would need to wrap [these] around the date column names since there is a number and text all together in the name.

Try this:

=SUMIFS({Project_Product Release Roadmap Intervalo 11}; {Project_Product Release Roadmap Intervalo 2}; Recurso@row; {Project_Product Release Roadmap Intervalo 3}; >=[Date1]@row; {Project_Product Release Roadmap Intervalo 3}; <=[Date2]@row)

This looks for the following:

The cell in this row (Recurso@row) in the Recurso column, matches content within the column {Project_Product Release Roadmap Intervalo 2}

If the date in this row ([Date1]@row) in the Date1 column is greater or equal to a date in the column {Project_Product Release Roadmap Intervalo 3}

If the date in this row ([Date2]@row) in the Date2 column is less than or equal to a date in the column {Project_Product Release Roadmap Intervalo 3}

Then it will SUM the content in the {Intervalo 11} range, if it meets the three criteria above. Is this what you are looking to do?

Cheers!

Genevieve

Genevieve P.

No problem at all! I'm glad that worked for you 🙂

M Underbrink

If you pull the range into a DATE() formula in your equation, would that work?

DATE({Meses Intervalo 3})

Angela Formiga

Hello,

Thank you for the answer.

Did not work. Appears the error below:

Angela Formiga

Anyone can help me?

Genevieve P.

Hi @Angela Formiga

You will need to reference a specific criteria after each {range}, instead of referencing an entire column after a range... this is why you're getting an error with your first formula but not with your second.

How is this formula being used in your destination sheet? Could you have specific dates listed in a Date Column in this sheet, and use that as your reference?

EX:

=SUMIFS({Project_Product Release Roadmap Intervalo 11}; {Project_Product Release Roadmap Intervalo 2}; Recurso318; {Project_Product Release Roadmap Intervalo 3}; >=[Date Column]$1; {Project_Product Release Roadmap Intervalo 3}; <=>=[Date Column]$2)

^Here I would have your two dates in rows one ($1) and two ($2). Would that work for you? If not, it would be helpful to see a full screen capture of both sheets (but please block out any sensitive data), with an explanation as to what each of your cross-sheet references are referring to.

Cheers,

Genevieve

Angela Formiga

Hello @Genevieve P

Thank you for the feedback. I tried what you say and did not work.

Returned the error Incorrect Argument SET"

My date columns are:

Maybe it is the format?

Angela Formiga

Any clue?

Genevieve P.

Hi @Angela Formiga

My apologies! I see that some of the instructions in my formula are repeated/incorrect (the <=>= is not a proper instruction). In addition, you would need to wrap [these] around the date column names since there is a number and text all together in the name.

Try this:

=SUMIFS({Project_Product Release Roadmap Intervalo 11}; {Project_Product Release Roadmap Intervalo 2}; Recurso@row; {Project_Product Release Roadmap Intervalo 3}; >=[Date1]@row; {Project_Product Release Roadmap Intervalo 3}; <=[Date2]@row)

This looks for the following:

The cell in this row (Recurso@row) in the Recurso column, matches content within the column {Project_Product Release Roadmap Intervalo 2}

If the date in this row ([Date1]@row) in the Date1 column is greater or equal to a date in the column {Project_Product Release Roadmap Intervalo 3}

If the date in this row ([Date2]@row) in the Date2 column is less than or equal to a date in the column {Project_Product Release Roadmap Intervalo 3}

Then it will SUM the content in the {Intervalo 11} range, if it meets the three criteria above. Is this what you are looking to do?

Cheers!

Genevieve

Angela Formiga

Hello @Genevieve P,

It is exactly that and worked perfectly :)

Thank you very much for the help. It was a big help.

Genevieve P.

No problem at all! I'm glad that worked for you 🙂