Number.app SumIFS not working

I see your values in all of your columns are left justified, including column G which is the important one in this case. The contents in the cells line up on the left side of the cells. Text naturally is left justified. Numeric values are naturally right justified like you see in cell H13 that has your numeric formula that is forcing it to be a numeric cell. This leads me to believe that your entire table is formatted as text. Numeric functions such as SUM, SUMIF, etc. ignore text. Even if the text looks like a number to you and me, it will be ignored.

You have mixed data in column G but it appears you can format the entire column as type "number" and this should fix this particular problem with SUMIFS. "Automatic" should also do it. It appears it was manually changed to text, otherwise the default "automatic" format would have automatically matched the type of data you entered and the numbers would be treated as numbers. When you change the cell format, all of the values in that column should switch to right justified.

Just to cover all bases here, if the values in column G are the result of formulas (not manually typed in), the formulas will have to be modified to generate numeric values, not text. The VALUE function is one way to do that.

Hi Erland,

I suspect that:

• The original document (2020) contains dates that were entered in 2020, but the cells are formatted to not display the year;
• The duplicate document (2021) retains the year 2020;
• Dates entered in 2021 will by default, have the year as 2021;
• SUMIF can not find a match with the 2020 dates and the new dates entered in 2021.

In the 2021 document, change the cell data format of dates in all tables to show the full date, including the year.

What do you see?

Regards,

Ian.

It should work so it is probably something wrong with your formula or your table, neither of which we have seen. If you post some screenshots and your formula it might help. But before you do that, I think your problem may have to do with dates, given that this is a copy of what seems to be a document based on dates.

If you have a cell that is "automatic" or "date and time" with anything that appears to be a date in it (a month and day , a month name, etc), it will have a year attached to it. That year will be the year in which you entered that date. So if you created your document last year, all of those cells should have the year 2020. If you are now trying to match them with dates you are entering this year, they will not match. January will not be equal to January because the one is January 2020 and the new on is January 2021.

With a screenshot and formulas we may be able to help you make your document more usable across years so you don't have this problem next year, too.