Sum of date with 2 conditions.

[Carly1985]

Hi everybody,

I'm trying to make the sum of all value in W when the date is between X and Y.

=AND(Data!$X2<=D$1,Data!$Y2>=Stats!D$1) --> True and false Works
=SUM(IF(Data!$X2<=D$1;SI(Data!$Y2>=Stats!D$1,Data!$W2,0),0)) --> Sum for one Row

When i try with SumIf or Sumifs, it's doesn't work :
=SUMIF(AND(Data!$X:$X<=Stats!D$1,Data!$Y:$Y>=Stats!D$1), True, Data!$W:$W)
=SUMIFS(Data!$W:$W, Data!$X:$X, "<="&Stats!D$1,Data!$Y:$Y,">="&Stats!D$1)

Do you have an idea ?

[daddylonglegs]

=SUMIFS(Data!$W:$W, Data!$X:$X, "<="&D$1,Data!$Y:$Y,">="&Stats!D$1)

Your SUMIF syntax won't work but the SUMIFS looks OK, what result do you get? Are Stats!D$1 and D$1 on different sheets or are they the same thing?

[Carly1985]

No it's on the same sheet. I added in order to make it clear, and I forgot one.

The result is "0".

[Carly1985]

I try with value :
X = Y = D1, =SUMIFS(Data!$W:$W, Data!$X:$X, "="&D$1,Data!$Y:$Y,"="&Stats!D$1) --> The value is good

[dredwolf]

it might help if you uploaded a small sample, as daddylonglegs said, your sumifs formula seems to be fine, so not sure where the problem is

[Carly1985]

Exemple.xlsx

I can upload the original file, because it is very big and it contain the database query. But this file is an exemple.

[dredwolf]

Two problems I see
1> the values in data columns X and Y, are mostly text, for what you are trying they should be numbers
2> when converted, the Y column values are all Larger Than the X column values, so you never reach a TRUE/TRUE state for the SUMIFS
(if X>=B1 and Y is larger than X, then Y CANNOT be <B1)
so get the numbers as numbers in X&Y of Data Sheet
then in B2 of stats:

Please Login or Register  to view this content.

Drag across
this gives the following results:
0 3 9 4 3 3 0 0 0 0 0 0 0 0

See attached

Hope this helps

[Carly1985]

Thanks, i check tomorow