Dec 07 2022 01:38 PM
Dec 07 2022 01:38 PM
I am using a sumifs to return a sum based on a category between 2 dates. My formula is correct because it works on some computers but not others. =SUMIFS('2022 accounts'!$C$2:$C$2361,'2022 accounts'!$G$2:$G$2361,$B19,'2022 accounts'!$A$2:$A$2361,">=12/1/2022",'2022 accounts'!$A$2:$A$2361,"<=12/31/2022") where '2022 accounts' consists of dates (column A) , items (column G) and values (column C).
Here is the problem that only occurs if I'm using either the online version of excel, or a particular one of 3 computers. If I change data (the value that is added) in column '2022 accounts'!$C$2:$C$2361', the whole table where the sumifs function is set changes to zeros. I can get it back by working in the excel desktop AP using either of the 2 computers that this does not happen on, by going into a zeroed cell, and hitting return at the end of the formula line.
If I take out the dates, and make it a sumif function, everything works fine.
The formulas works fine if the file is uploaded as a google sheet, and it also works if I make changes to the file on my cell phone. I've also checked to see if my excel settings are the same on the computer it works fine on, and the one where it consistently returns '0'. I've also tried reinstalling office on the computer that returns '0's - to no effect. I'm beginning to wonder if I need to reinstall windows! Really don't want to go there.
So I am wondering if anyone else has had a similar 'returns zeroes' issue with sumifs formulas where dates are involved?
Dec 07 2022 01:43 PM - edited Dec 07 2022 01:44 PM
Do the computers use different date formats? Try the following:
Enter the date 1-Dec-2022 in a cell, e.g. in X1, and the date 31-Dec-2022 in another cell, say Y1.
Change the formula to
=SUMIFS('2022 accounts'!$C$2:$C$2361,'2022 accounts'!$G$2:$G$2361,$B19,'2022 accounts'!$A$2:$A$2361,">="&X1,'2022 accounts'!$A$2:$A$2361,"<="&Y1)
Dec 13 2022 07:35 AM
@Hans Vogelaar Many thanks. It is a simple and elegant solution. It does not account for why my original formula works on one computer but not another, even when all the dates are formatted the same. But I'm not an excel engineer, and I have my solution.