SUMIFS Not Working

Hi quinn,

It's worth restating for clarity. I don't think it's the Pop-up Menu. Again, here is my SUMIFS function:

I think Numbers turns "=October" into 10/1/CURRENT-YEAR, instead of keeping it say, 10, or 10/1/ANY-YEAR.

When I open a 2019 document, the SUMIFS initially display correctly. When I touch the SUMIFS cell to check the function, the number returns zero and I cannot UNDO the action. Gone forever. The pop-up menus still retain their values, but somehow the function has changed, and they are not being picked up.

Hope that makes sense. In every situation here, I strive to use the words of the months, but Numbers is doing autoformatting on both the pop-up menu and SUMIFS side.

SG wrote:

“avoid "hard-wiring" specific criteria into a formula. Put the criteria in cell(s) and have the SUMIFS refer to those.”

Warning: see this link in this forum:

Does SUMIF convert a text string into an expression?

All codes in column A are formatted as Text.

Column B calculates the length of each code: LEN(A).

Column C calculates the SUM of the length of each code (which has to be the same as the length in column B because each code is unique): SUMIF(A;A2;LEN(A)).

However the sum of the length for each of these codes is wrong:

SUMIF adds the length of the codes 110/1, 1100/10 and 440/4 together — as well as the length of the codes 130 and 130/1, and the length of the codes 40 and 40/1.

Paul.

Hi Paul,

It has been well established that using "/" in a serial/id number is problematic. Not a best practice. Someone should research their keyboard and come up with alternatives.

I do hope you have submitted a bug report to Apple. Perhaps they will rescue you from this foolishness.

quinn

Or perhaps one should say Numbers should not try to interpret a value formatted as Text (I don’t think it actually converts it).

The problem with that is that sometimes I actually WANT Numbers to interpret, as for example when I enter a time as Text instead of Date & Time because I don’t want today’s date attached to it which will mess up matches tomorrow but I still want to do time arithmetic on it (which Numbers will happily do on times entered as Text).

Anyone, I suspect best practices (in both Numbers and Excel) is to avoid special characters in values that will be used for lookups or SUMIFS, etc.

SG

I am having the same issue. Could it be related to date switch to 2020. I had it open on Dec 31 and it worked perfectly. Opened same file again Jan 1 and it showed zeros all over. All my dates are entered with day/month/year formatted as date. Will play with it but not obvious what to change if it worked just fine a few days ago...

Looks like you didn't read the original post thoroughly. Original poster had working SUMIFS in 2019 that started returning zeroes in 2020. My observations reflect that to a tee.

If you haven't had this happen to you, you can definitely sit this one out.

Hi Phaedon2,

Reviewing upthread it seems you too had results rebuilding the popups.

But, not my dog/fight right now. I may be thinking of this thread when I do upgrade. If it is still open I may have an experienced based opinion to share.

quinn

In the table below the cells in column A were TEXT formatted before entering the codes.

Six different codes in column A, using COUNTIF in column D and SUMIF in column E.

However:

• ‘3’ and ‘0003’ are considered identical when using COUNTIF/SUMIF
• “6-3”, “6/3”, “06/03” and “06/03” are considered identical for COUNTIF/SUMIF

Category does it right.

The same table, adding Category for column A: “Unique Codes (Text Formatted)”

In column D: Count: =1 for all codes, meaning all codes in column A are considered different. 

In column C: Subtotal: = 1, 2, 3, or 5, thus the length of each code individually. 

Paul

"Interpreting" is indeed the correct word in this case.

However Numbers should stop "interpreting" the content of a cell as soon as the cell is formatted, if not set to "Automatic".

e.g. rather than using the Text format for entering Time values, Numbers should stick to its formatting rules:

• When a cell is formatted as "Date", Numbers should not add (concatenate?) automatically a time on the date (by default the Time should be set to "none").
• When a cell is formatted as "Time", Numbers should not add (concatenate?) automatically a date on the time (by default the Date should be set to "none").

This approach would make arithmetic on Dates and Times straight forward.

Paul.

For data entry that wouldn’t be as convenient for me sometimes. I like it the way it is. I just use unambiguous values for lookups and SUMIFs, etc.

SG

Well, the screenshots you provided do not show the same portion of the year, so it is hard to help. Please show a screenshot where the summary (the table "Month by Month") and the table "Expenses" show the same portion of the year so that we can perform manual operations to set expectations about the results.

one comment for the table "Month by Month". You have the month names going across the top of table and category names going to the left side.

adjust your formula to:

1) use those headers

2) be exactly the same

select cell B4 (the same cell you have selected in your screenshot)

=sumifs(Expenses::Amount, Expense, $A4, Month, B$1)

shorthand is:

B4=sumifs(Expenses::Amount, Expense, $A4, Month, B$1)

the "$" in "$A4" forces an absolute column reference (that is no matter where you fill this formula, the reference will also look in column A)

the "$" in "B$1" forces an absolute row reference (that is no matter where you fill this formula, the reference will also look in row 1)

now select cell B4, copy

select cells B2 thru the cell M11, paste

OTHER SUGGESTIONS:

• make sure the values in column C of the expenses table are formatted as numbers (not text)
• make sure the categories match between the "Month by Month" and "Expenses" tables

I was puzzled by some of this thread, because in the Data Format dropdown you can format a cell as Automatic OR Text OR Date & Time OR Pop-Up Menu.

After experimenting, I think the trick here, if you want Numbers to recognize the month names in a Pop-Up Menu as Text and not as Date & Time, is to first format cells as Text and add your desired menu choices in those cells. Then select the cells and format them as Pop-Up Menu.

If you want Numbers to recognize the month names in a Pop-Up Menu as Date & Time (which can be useful in some situations but usually not if you are using SUMIF or SUMIFS) then first format the cells as Automatic (not Text). Add the desired menu choices in those cells. Then select the cells and format them as Pop-Up Menu.

SG

Hi Phaedon2,

While I am currently using Numbers in Mojave (yea, me), I am wondering if you have tries SG's suggestion that you first format your cells as text before you format them as a popup.

Sometimes Apple takes to improve things in ways that confound our accustomed usasge. Is it possible that they coded popups to remember their previous format? I could imagine an upside if this is the case. I cannot test this on my current setup but if SG states that text converted to popup works where automatic converted to popup fails it is worth your time to test it out.

My 2¢ from the sidelines.

quinn

SG,

the issue is this:

Numbers tries to convert a TEXT FORMATTED string into an operation, time, day, month, year, date, currency, ... whatever!?

Numbers must not try to modify a TEXT FORMATTED string. Doing so violates the meaning/purpose of formatting cells.

Paul.

From the Numbers User Guide for Mac:

Date and time

Numbers automatically interprets text strings that you type in a recognised date format as dates (for example, 10/06/17).

1. Select the cells you want to format.
2. In the Format 
3.  sidebar, click the Cell tab, then click the Data Format pop-up menu and choose Date & Time.
4. Click the Date pop-up menu, then choose a format.
5. If you choose None, no date is displayed in the cell, even if a date is entered and used in date and time calculations.
6. Choose a format from the Time pop-up menu.
7. 
   
8. If you choose None, no time is displayed in the cell, even if a time is entered and used in date and time calculations.

If you don’t enter both a date and a time, Numbers adds a default value for you. For example, if you type “1:15 pm”, Numbers adds today’s date by default.

— — — — 

“If you don’t enter both a date and a time, Numbers adds a default value for you.”

Suggestion: Numbers should not add anything at all and stick to the chosen format: “None” = “None”.

Paul.