SUMIFS : can't figure how to create formula

Question

Level 1

13 points

SUMIFS : can't figure how to create formula

Hello

I have a relatively simple requirement : to sum some cells if two other conditions are met. I've tried to create the formula, read the Apple Numbers formula guide...but still can't get it right - it keeps returning syntax error. Can someone please help?

Here is a sample table:

The data I have (far more complex and comprehensive than depicted) is arranged as on the left, under the heading 'FULL DATA'. This data represents the hours worked with a given client (broken down into 'billed' and 'actual' by a one of two employees (denoted by either 's' or 'm' in Column D). An employee can service multiple clients per day, hence multiple entries per employee, on the same day).

I want to create a summary table as depicted on the right, under the heading SUMMARY' Ideally this table would reside on a different sheet - my spreadsheet file has about 15 different sheets, and it would make most sense if the 'SUMMARY' data sat on its own sheet. I understand this creates a slight complication. The sheets being referred to are called 'January', 'February'.

Basically I want a formula that sums individually the number of 'billed hours' & 'actual hours' worked by employee 's' on the a given date. I'll need a similar formula for employee 'm', but am sure I can figure that part out.

I'd be most grateful if someone could help me out with this.

Matthew

Auckland, NZ

Posted on Jul 18, 2019 12:49 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Jul 18, 2019 12:46 PM in response to MattRA

Hi Matthew,

The only differences in the structure of this table compared to the example table, is the two added columns between Date and Billed, and the three columns added between Actual and Code ('who" on my example).

Inserting those columns moves Billing to column E, Actual to column F, and Code / who to column J.

In the Summary table:

the formula in B2 is now: SUMIFS(Data::E,Data::$B,$A2,Data::$J,”=“&LEFT(B$1,1))

the formula in E2 is now: SUMIFS(Data::E,Data::$B,$A2,Data::$J,"="&LEFT(E$1,1))

Each formula is entered in the cell named above, filled right one column, and filled down to the bottom of the table.

The screen shot shows your Data table on the left (with the new cells left empty), and the Summary table on the right. The formula below the table is the one in the selected cell, B2 of Summary.

Further information regarding SUMIFS (and other functions supported in Numbers) can be found in the Function Browser, visible when any cell containing a formula (or in which an = sign has been typed) is selected.

Regards,

Barry

Reply

Answer 2

Barry

Level 9

60,023 points

Jul 21, 2019 1:56 PM in response to MattRA

Hi Matthew,

I don't immediately see a reason for the formula not to work.

The one shown should be returning 4, Sam's hours for the three job on 1/12.

Upper/lower case shouldn't matter to the 'code' conditions—I checked that in my sample, matching case for Sam's code and using different cases for Matthew's. Both worked.

I'm a bit puzzled by the column label references for the columns on the data table and column letters and row numbers for the two cells on the summary table. It may just be that Summary has no row 'names' and Numbers won't do a mixed style (name8 or Bname) and defaults to the 'standard form.

Some trouble-shooting steps:

As you do each step, watch for and note any changes in the result returned by the formula in Summary::B2.

Are there any invisible characters in the code list (eg. a space before or after the one letter code? Try retyping the entries in the rows for 1/12.

Re-enter the dates in the 1/12 entries, using a different format (If you have set the format in the Inspector, Numbers will accept the date, and change it back to the 1/12 format. (which is OK).

Do the same with the date in A2 of the summary table.

In Numbers Preferences, try unchecking the box for Use Header names as labels.

Try removing one of the test-values,condition pairs (Table 1::date, A2,) then confirming the revised formula.

Re-insert the first pair and remove the other pair.

Let me know your results.

Regards,

Barry

Reply

Answer 3

Barry

Level 9

60,023 points

Jul 20, 2019 12:09 AM in response to MattRA

Hi Matthew,

Cleaning up my IN box tis evening and noticed this comment, which I hadn't replied to:

"I couldn't understand the "="&LEFT(B$1,1)) reference. I assume that to be an effort to extract the letter 's' from Column B. Could we not just do something like Data::$D,"*s*" ? Anyway, I'm probably just showing my ignorance here!"

Correct assumption, and yes, you can write the actual text into the formula if you find that easier.

The essential part is to make the search value match the appropriate value in the column being searched. I usually do this by using the search value ("s" or "m" in this case) as the label of the column where the summary for that value will be calculated, then referencing the first cell of that column to collect the search value. This allows writing one formula for several columns of summary calculations, then filling that formula right into each column where that calculation is to be made.

You had already established codes to be searched, and labels for the columns where the summary calculations were to be made, so I revised what would have ben simple sell reference to the cell containing the 'x : Billed" and "x : Actual" labels to extract the matching search code from those cells.

Regards,

Barry

Reply

Answer 4

MattRA Author

Level 1

13 points

Jul 18, 2019 4:02 AM in response to Barry

Hi Barry

Thanks so much for the time and effort you've gone to here - it's so appreciated! Unfortunately I couldn't get it to work - almost certainly a result of my sample setup being different from the actual table I use. I decided that having a separate sheet to summarise the hours isn't going to work, as that would need all days in a calendar year listed on one sheet. So I think it'll just be a separate 'summary' table on the same sheet as the data for a given month.

I couldn't understand the "="&LEFT(B$1,1)) reference. I assume that to be an effort to extract the letter 's' from Column B. Could we not just do something like Data::$D,"*s*" ? Anyway, I'm probably just showing my ignorance here!

Here's a screen grab of some actual data for July. Currently, in Column J, I use 's' to designate jobs done by an employee called Sam, and every other row is a job performed by me. I can easily change that so that each employee and myself have a specific code : eg S for Sam, M for Matthew etc. The other letters (*eg x, e, c etc) in Column J are used to track other aspects of that client.

For the purposes of making it clear for you what I'm trying to achieve, from the data below, you can see that on Jul 1, I did 6 'actual hours' (rows 4,5,6). Sam also did 6 'actual hours' (rows 7,8,9,10)

Does having the actual table data there make it easier?

Matthew

Reply

Answer 5

MattRA Author

Level 1

13 points

Jul 21, 2019 4:09 AM in response to Barry

Hi Barry

Thanks so much for sticking with me on this one! In understand your explanation for naming the columns as "S : billed" etc.

I tried to mimic the setup you have (ie summary table alongside the data table, in the actual working spreadsheet) and copy/pasted the formulas in, then tweaked them (as they were returning REF errors) and I'm pretty sure I've got them right. I've included 2 screenshots so you can see the formula I have in B2. I then filled B2 right to C2, then filled B2 & C2 simultaneously down to row 5. Unfortunately it returns zero results. I checked the format of both Date columns (column B in Data table and Column A in Summary table) to ensure the same date formats are being used, with no time component : no change. I'm at a loss to explain why it isn't working - could it be that my Data table is labelled as such? Could it be a capitalisation issue? Presence of other letters mixed in with 's' and 'm' in the Code column? Conditional formatting in Code column?

Hopefully we can solve this as it will be so invaluable! About to post another question that will save me lots of time too.

Thanks again

Matthew

Reply

Answer 6

MattRA Author

Level 1

13 points

Jul 22, 2019 3:58 AM in response to Barry

Hi Barry

Making progress - but still an issue, although at least have identified it! I did as you suggested (re-entering date cells, checking for hidden characters, unchecking 'Use Header names as labels.'

I gave the data table the name 'Data' - this helped when copying/pasting your formulas.
I figured out why my Summary table wasn't adding up ALL of Sam's E or F cells for a given date : if the "Code" column of the Data table contains any letters (other than 'S' or s'), cell B2 of Summary table won't sum the entry in Data's E or F column. If required, I can make a separate column that is used solely for classifying which employee the row relates to (thereby that cell would only ever contain one letter), however would prefer to have one 'Code' column that I can put any number of letter codes in.

You can see in the example below, that for all 1/12 entries, I removed all letters from 'Code' (leaving only 's') so Summary::B2 & C2 return correct result. For the 4/12 entries, rows 13 & 14 contain 'se' in the 'Code' rows - and this causes Summary:: B5 & C5 to NOT sum those rows.

Hopefully this can be sorted?

Matthew

Reply

Answer 7

MattRA Author

Level 1

13 points

Jul 24, 2019 2:20 AM in response to Barry

Hi Barry

Thanks SO much for your help - I really appreciate it. The changes you've engineered (via your ridiculous formula skills) will save me so much time, and make me more accurate.

Matthew

Reply

Answer 8

Barry

Level 9

60,023 points

Jul 18, 2019 2:29 AM in response to MattRA

HI Matthew,

Here's a copy of your sample table, split into a Data table and a Summary table. The formula shown below the tables is entered in the selected cell, Summary::B2, then filled right into column B, and filled down to the bottom row of the table.

The formula cannot be filled further right, as it would then be trying to sum columns to the right of D and E on the Data table.

More below the image.

Note: copying and pasting the formulas:

Summary::B2: SUMIFS(Data::B,Data::$A,$A2,Data::$D,”="&LEFT(B$1,1))

Summary::E2: SUMIFS(Data::B,Data::$A,$A2,Data::$D,"="&LEFT(E$1,1))

Each formula is entered into the designated cell, then filled right one column and filled down to the bottom row of those two columns. The formulas are identical, except for the part shown in bold.

Regards,

Barry

Reply

Answer 9

Barry

Level 9

60,023 points

Jul 22, 2019 10:24 AM in response to MattRA

"Hopefully this can be sorted?"

It can, but the solution places some limitations on the codes that can be placed in the column:

each code identifying a person must be the same length (currently a single letter—other lengths would require further changes to the formula).
the 'person' code must be the first code in the string.
other restrictions may arise, depending on the ue to be made of the other codes in the string.

Revised formula for Summary::B2: SUMIFS(Data::E,Data::$B,$A2,Data::$J,"="&LEFT(B$1,1)&"*")

The change, shown in bold, adds a 'wild card' to the code condition, making it require the first letter of B1 (S) followed by any other characters, or no characters at all.

Make the same change to the formula in Summary::E2.

Fill each formula one cell to the right,

Fill each pair down to the last row of the Summary table.

Regards,

Barry

Reply

Answer 10

MattRA Author

Level 1

13 points

Jul 22, 2019 1:12 PM in response to Barry

Hi Barry

That's great, although it sounds to me like creating a dedicated column for the 'person' code is a wise move on a number of fronts. Have revised the table - it now looks like this:

It appears to work correctly - have cross-checked the values (manually) and they do match. Woohoo!! One thing I did notice (when I discovered a calculation error in Summary::E5) was that the formula seems sensitive to the code in Data::J. It doesn't seem to be troubled by capitalisation, however I noticed that Summary::E5 was 'down' by 1.75 and for some reason it hadn't summed Data::E10. There appeared to be only a capital M in Data:J10 but when I replaced it (with an 'm') the result in Summary::E5 updated to the correct amount. Perhaps a space?? I do notice that sometimes, when I fill in the codes, Numbers tries to auto-fill (?) results and sometimes won't enter the typed value to 'stay' and move to the next cell - even when 'enter' or 'return' is pressed. Could this be causing the confusion?

Another question related to table names : unless I've misunderstood things, each table within a spreadsheet should have a unique name - for ease of reference. If this is the case, I'll have to name the 2 tables (Data, Summary) within each 'Month' sheet (remember I have one sheet for each calendar month - so 12 'Month' sheets) something like this : Dec Data, Dec Summary. Or would DecData or Dec_Data be better? Otherwise, when a formula refers to 'Data' it could be any one of 12 'Data' tables.

Matthew

Reply

Answer 11

Barry

Level 9

60,023 points

Jul 22, 2019 2:14 PM in response to MattRA

MattRA wrote:

Could this be causing the confusion?

Possibly. The condition requires an exact match to include the sum-value on each row to be included in the sum, with the wild card, it should match "S" or "S ", but would not match " S".

Another question related to table names : unless I've misunderstood things, each table within a spreadsheet should have a unique name - for ease of reference. If this is the case, I'll have to name the 2 tables (Data, Summary) within each 'Month' sheet (remember I have one sheet for each calendar month - so 12 'Month' sheets) something like this : Dec Data, Dec Summary. Or would DecData or Dec_Data be better? Otherwise, when a formula refers to 'Data' it could be any one of 12 'Data' tables.

Having a name unique within the document isn't required. But if every Data table is named "Data", Numbers will require the name of the Sheet that table is on to locate the right Table.

You could name each data table with the name (long or short form) of the month whose data it holds.

The simplest approach might be to follow the example in the Personal Budget template—enter ALL of the data on one table, then extract each months data on a Summary table for that month (or, since you already enter the full dates into the Summary table, and that date is one of the conditions for the existing SUMIFS formula, you may need only a single Data table and a single Summary table.

Make the Summary table large enough to summarize all 31 days of the longest months, and use a formula to construct those dates from the first-of-month date entered in a single cell.

If you need a permanent record of each month's summary, Print the Sheet containing the Summary table to a PDF document at the end of the month, and before entering the date for the new month.

Regards,

Barry

Reply

SUMIFS : can't figure how to create formula

Similar questions