You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Number.app SumIFS not working

Hello from Sweden

I have a Numbers document (2021) with sheet A and B. On sheet A the formula SumIFs works OK - but not on sheet B. And from sheet A I cannot get the formula SumIFS to grab data from sheet B.

Sheet A has four tables and sheet B has one tables.


This Numbers document (2021) is a duplicate from last years Numbers document (2020) that works perfectly with all the different formulas.


Why can't I get the sheet A to grab data from sheet B with the formula SumIfs?

I am using Numbers version 10.1


Please help

/Erland

Posted on Apr 9, 2021 3:28 AM

Reply
Question marked as Top-ranking reply

Posted on Apr 9, 2021 5:16 PM

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.

6 replies
Question marked as Top-ranking reply

Apr 9, 2021 5:16 PM in response to erland32

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.

Apr 9, 2021 4:24 AM in response to erland32

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.

Apr 9, 2021 4:16 AM in response to erland32

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.

Apr 13, 2021 9:23 AM in response to Badunit

Thank you Badunit and Yellowbox.


You both pointed me into the right direction. This is the background of my problem:

From my book keeping software I will export data to the Numbers spread sheet as a text file. The different amount contains a " . " (see example above in column F) which Numbers don't like. I've changed it to " , " with the help of AppleScript and TextEdit, and now Numbers formula SumIfs works perfectly.


Once again Thanks for all your help

/Erland from Sweden


Number.app SumIFS not working

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.