SUMIFS Error returning 0

[naucoin07]

Hello, I am trying to use SUMIFs with multiple criteria and a date range to return the sum using two excel sheets. I keep getting zero as my result... I made the fake charts below for help
Excel One - looking up units from excel two

=IFSUMS([Excel 2]$C$2:$C$5,[Excel 2]$A2:$A5,[Excel 1]$A2,[Excel 2]$B2:$B5,">="&[Excel 1]B$2,[Excel 2]$B2:$B5,"<="&[Excel 1]C$2)

Item	Start	End	Units
A	1/02/2020	1/07/2020
B	1/08/2020	1/14/2020
C	1/02/2020	1/07/2020

Excel 2

Item	Invoice Date	Units
A	1/05/2020	10
B	1/09/2020	15
A	1/07/2020	20
C	1/03/2020	20

 

[jasonb75]

It looks like you have a few $ symbols in the wrong places which would affect when you try to fill down, but other than that I can see no reason why it wouldn't work if your data is valid.

=SUMIFS([Excel 2]$C$2:$C$5,[Excel 2]$A$2:$A$5,[Excel 1]$A2,[Excel 2]$B$2:$B$5,">="&[Excel 1]$B2,[Excel 2]$B$2:$B$5,"<="&[Excel 1]$C2)

Noting that your data is aligned to the left side of the cells, that can be an indication that the numbers and dates are in text format which could be a problem for the formula. It is possible that your data is fine and that this is being caused by the way that you have copied your sample to the forum.

 

[naucoin07]

It looks like you have a few $ symbols in the wrong places which would affect when you try to fill down, but other than that I can see no reason why it wouldn't work if your data is valid.

=SUMIFS([Excel 2]$C$2:$C$5,[Excel 2]$A$2:$A$5,[Excel 1]$A2,[Excel 2]$B$2:$B$5,">="&[Excel 1]$B2,[Excel 2]$B$2:$B$5,"<="&[Excel 1]$C2)

Noting that your data is aligned to the left side of the cells, that can be an indication that the numbers and dates are in text format which could be a problem for the formula. It is possible that your data is fine and that this is being caused by the way that you have copied your sample to the forum.

Turns out I had the date typed out WRONG.... 20220 vs 2020 and my eyes did not catch it...THANK YOU