how to change C:C;"<"&Q2;C:C;"<"&S2 for AND("<"&Q2;"<"&S2)?

[juan jimenez]

hi,

i am working with this formula where C:C, A2 and S2 are dates:

=SUMAR.SI.CONJUNTO(Hoja1!$K:$K;Hoja1!$G:$G;P6;D:D;">1";C:C;"<"&Q2;C:C;"<"&S2)

i wonder if it is recommended to change the last 2 conditions for a "AND" formula to reduce the number commands.

If so, would it be so easy as:

replace

C:C;"<"&Q2;C:C;"<"&S2

with

C:C;AND("<"&Q2;"<"&S2)

Because it is not working with my excel 2016

Thank you, juan

[#2]

@juan jimenez 

You can replace

C:C;"<"&Q2;C:C;"<"&S2

with

C:C;"<"&MIN(Q2;S2)

By the way, shouldn't all columns referred to be on Hoja1 ?

[#3]

Thank you Hans,

there was a mistake in my formula.
what i wanted to replase is
C:C;">"&Q2;C:C;"<"&S2
Regarding "hoja1", i do not know why sometimes it takes the reference to the sheet i am working and sometines not.
can you help?


with

[#4]

@juan jimenez 

Just in case, what's wrong with Excel 2016? SUMIFS() shall work in it.

[#5 · Accepted solution]

@juan jimenez 

If the cell with the formula is on Hoja1, you can use

=SUMAR.SI.CONJUNTO($K:$K;$G:$G;P6;$D:$D;">1";$C:$C;">"&Q2;$C:$C;"<"&S2)

If the cell with the formula is on another sheet, use

=SUMAR.SI.CONJUNTO(Hoja1!$K:$K;Hoja1!$G:$G;P6;Hoja1!$D:$D;">1";Hoja1!$C:$C;">"&Q2;Hoja1!$C:$C;"<"&S2)

You'll have to keep on referring to column C twice; you cannot combine the two conditions in SUMAR.SI.CONJUNTO.