Having a problem with SUMIF

Dave,

All I can say is that you may be making it too complicated. Here's a simple implementation with your own data, as posted in your question:

I suggest that you try this simple approach, than add any additional columns that you may need.

Jerry

Hi Dave,

You could try making sure the month names in B1 and down (in your original screenshot) are text. One way to do that is type them as 'January 'February , etc. (with a leading single quote, that disappears after you hit enter).

I'm guessing Numbers thinks they're date-time strings, so they won't which match the results of your MONTHNAME(MONTH()).

SG

Hi Dave,

What I am seeing in your screen shot is that the two ranges are not the same size. Somehow in your Date range the entire column including what should be the header rows is selected. Try dragging the pink box out of those top two rows.

Quinn

DaveH123 wrote:

Apparently, my post didn't include everything. The example you suggest is really the same as the one I posted that works, just yours uses the explicit columns. The formula that doesn't work is this:

=SUMIF(MONTHNAME(MONTH(Checking::Transactions::Date)),B1,Checking::Transactions: :Debit Amount)

Here is a screen shot of the broken formula and sheet and wasn't in the original post.

Message was edited by: DaveH123 The image was embedded. Instead of pasting, I'll use the Insert Image button. Oops, Grab doesn't save images in the format that the discussion likes.

Dave,

There are two problems with your approach. You do have unequal ranges, as if obvious in the screen shot in your last post and as was reported by Numbers. More importantly, you are not using SUMIF properly.

The syntax for SUMIF is:

SUMIF(test-values, condition, sum-values)

"Test-values" and "sum-values" are range specifications.

MONTHNAME(MONTH(Checking::Transactions::Date)) is not a range specification and SUMIF will not work with that approach.

Jerry

Dave,

Checking::Transactions::Date would be a range. When you wrap it in MONTHNAME(MONTH()) it is no longer a range. I'm not sure exactly what it is. Not sure where you were going with all that nesting!

If you uncheck this in Numbers > Preferences:

then it may be easier to recognize the ranges in your formulas.

Jerry's simple example is the way to go to get things working, though I would input the months as 'January and 'February etc., i.e. as text rather than as shown. Otherwise, Numbers internally stores what look like simple month names as date-time strings (i.e. with a specific month, day, year, and time) so January (actually 1/1/2014, 12:00:00 AM on my machine) will work this year but maybe not next year, when it will look the same but the year may no longer match.

SG

Dave,

It's so easy to fill a column with month names, I don't know why you would fuss with functions to extract them from a date. Type January in the first cell and Fill Down.

Jerry

SGIII wrote:

Dave,

Checking::Transactions::Date would be a range. When you wrap it in MONTHNAME(MONTH()) it is no longer a range. I'm not sure exactly what it is. Not sure where you were going with all that nesting!

If you uncheck this in Numbers > Preferences:

then it may be easier to recognize the ranges in your formulas.

Jerry's simple example is the way to go to get things working, though I would input the months as 'January and 'February etc., i.e. as text rather than as shown. Otherwise, Numbers internally stores what look like simple month names as date-time strings (i.e. with a specific month, day, year, and time) so January (actually 1/1/2014, 12:00:00 AM on my machine) will work this year but maybe not next year, when it will look the same but the year may no longer match.

SG

SG,

MONTHNAME(MONTH(Checking::Transactions::Date)) would be the name of the month of the Date entered in the current row of the column Date in table Transactions in Sheet Checking. Certainly not a Range, as you say.

Jerry

MONTHNAME(MONTH(Checking::Transactions::Date)) would be the name of the month of the Date entered in the current row of the column Date in table Transactions in Sheet Checking.

Jerry,

That is true if the expression is in the table where the Date column is, as shown here in column D:

But it's not true when the expression is in another table referencing that table:

In this example it seems to generally think it's the last value it's encountered in the range referenced within. But it can get a little confused. Maybe it's all that snow removal!

Again, I would supplement your advice about keeping it simple by adding that is best not to just enter month names in the column. That's because Numbers's smart date recognition (too smart, I think) promptly attaches a day, year, and time, as shown here:

So it won't match as expected across years. Generally better to make sure the month names are text by first typing a '.

SG

SG,

You can outsmart the automatic Date recognition by preforrmating the cell of the first entry as Text or by appending a leading apostrophe to the first entry. The column will still Fill properly with Month names.

Jerry

You can outsmart the automatic Date recognition by preforrmating the cell of the first entry as Text or by appending a leading apostrophe to the first entry.

Jerry,

That's exactly what I'm recommending. Don't just enter month names or you'll likely end up scratching your head after the year changes. Just entering them builds a stealth time bomb, because they may look like simple month names but they aren't. They're year-specific. I say, "Outsmart the smart date recognition now or eventually it will outsmart you!"

SG

SG,

The category names in Table 2 were entered as Text Month Names, and as you can see, they stay that way.

Jerry

Jerry,

I'm not sure what you are driving at here. For the month names in column A below I followed your instructions upthread exactly: "Type January in the first cell and Fill Down." As you can see, following those instructions results in what looks like month names. But, under the hood, they are not month names. They are date-time values. And they stay that way.

I'm recommending that people take care not to just "Enter January and (Auto)fill Down" when matching on month names will be important, as it is here. Instead, be sure to outsmart the smart date recognition so that the values do not end up as date-time values with a specific year attached that won't match as expected in subsequent years.

One way is to do this, as described upthread, is to type 'January (with a leading apostrophe) and fill that down. Another is to specifically format the cells beforehand. And be cautious of month names in Pop-Up Menu.

Don't you think this is good advice? It seems like a small point but it's not if you're trying to match on month names. Certainly, knowing this would have saved me headaches before I was fully aware of the consequences of smart date recognition.

SG

As I mentioned, upthread, if you enter into "Automatic" format, your result will be as you illustrate, but if you enter into "Text" format or if you begin your first entry with an apostrophe, you will get a text result, and it will Fill as text and will remain as text.

That's all I'm getting at. It will be solid.

Jerry

Got it. I think we're on the same page. Sometimes I wish I could turn off the default to Automatic or the smart date recognition or both.

SG