Sumifs with several criteria, when I add dates criteria the sum goes to 0

I have a sumifs with several criteria. They sum fine but when I add the dates criteria, it all goes to 0. I'm adding something like this to the criteria options $E$2:$E$39654,"<"&E2,$E$2:$E$39654,"=>"&E2-365). Column E is the dates column.

I expect non-zero sums(all non specific time ranges add fine). Any idea what might be the issue.

I think your last criteria should be this:

$E$2:$E$39654,">="&E2-365

Hope this helps.

Pete

I attached a sample. I expect cell J2 to be 135 but it's 57. Thanks for the responses

Thanks for the responses. I figured it out.

Not so quick... I don't understand why there is such a stark difference between column J and column K. In Column J, the formula does not seem to be covering the whole data for the year. I've attached a new sheet.

Originally Posted by JohnTopley

Try

Please Login or Register  to view this content.

I don't think this helps. The winner is sometimes a loser but the id stays the same.

You can check for id - 201523, you will find that there are only two entries => one row when 201523
is the winner and one row when 201523 is the loser. For the later date the sum(1/8/2007) will be 57 + 39 = 96 while for the earlier date, it will be just 57. Either way it's not 117 as your solution suggest.

I don't understand the intuition behind the formula. Can you verbalize what you are trying to do?