Sum values based on certain letters in another column

Question

Level 1

13 points

Sum values based on certain letters in another column

Hello

1. I'd like some help with devising a formula that sums certain values depending on the presence of 'coding' letters in another cell.

Essentially I have a row of information that contains several different values in individual columns. I then want to have a 'coding' cell that pertains to that particular row, where I type a certain letter or more likely letters. These letters tell provide additional information about the values in the row. I want to sum certain columns based on the presence of certain letters in the 'coding' cell, however my current use of the SUMIF formula (e.g. SUMIF(g4:g6,"=m",a4:a6" only sums values in a4:a6 if ONLY the letter 'm' is found in g4:g6 - whereas I need the formula to hunt out the letter 'm' even if there are other letters in the same cell with it - e.g. cell g6 which contains 'mr'

2. Also, I'd like to have the spreadsheet summarise locations occurrences (frequency) and sales from each location. I may have up to 40 locations to handle - so how can I write a SUMIF type formula that takes in all the locations? Does it have to be a hug long formula that lists all 40 locations??

Hopefully someone can help!

Thanks in advance

Matthew

PS - this is just a sample spreadsheet - the real one is normally 100 rows long for each month, so I need to get the summarisation functions as automated as possible!

MacBook Air (13-inch, Early 2014), OS X El Capitan (10.11.3)

Posted on Apr 29, 2016 3:58 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,258 points

Apr 29, 2016 6:45 AM in response to MattRA

You can use wildcards in SUMIF or SUMIFS, like this:

=SUMIF(G,"*m*",A)

BTW your layout looks very "Excel-like". Suggest you break up your table to take advantage of the Numbers design. Here, my row 6 is defined as a Footer Row. That way I can just refer to column G without worrying about which defining a particular range.

SG

Reply

Answer 2

Yellowbox

Level 7

20,329 points

Apr 29, 2016 7:29 AM in response to SGIII

Hi SG,

You can use wildcards in SUMIF or SUMIFS

That helped me 🙂.

BTW your layout looks very "Excel-like". Suggest you break up your table to take advantage of the Numbers design.

I agree.

Regards,

Ian.

Reply

Answer 3

SGIII

Level 8

35,258 points

Apr 29, 2016 12:41 PM in response to Yellowbox

Hi Ian,

Thanks for the shout-out. (I'm hoping "shout-out" means the same there as it does here; with Australian one never knows🙂.).

SG

Reply

Answer 4

MattRA Author

Level 1

13 points

Apr 29, 2016 1:51 PM in response to SGIII

Hi SG

Thanks so much - will try including the wildcard into my existing formula to see if it produces the result I need.

That spreadsheet I included was just a sample to display the data, however I do fear my spreadsheets to look a little Excel-like! I'd like to learn how to improve them (I typically have a spreadsheet that has 12 different tabs (one for each month) - these contain all the boring data. I then have a couple of 'summarisation' tabs that summarise the data on 12 month tabs. It's a bit awkward flicking back between the various tabs - is there a better way? Are you suggesting 'tables within tables' on the same tab? I'm also not sure how to define a row as a header row, and how it then reacts differently to a standard row.

Also - any ideas on my location summarising formula? (point 2 above).

Thanks so much,

Matthew

Reply

Answer 5

SGIII

Level 8

35,258 points

Apr 29, 2016 6:26 PM in response to MattRA

Here's one way you could summarize the data by location:

The formula in B2, filled down:

=SUMIFS(Data::A,Data::E,A)

The formula in C2, filled down, is:

=COUNTIF(Data::E,A)

A row can be defined as a Header Row or Footer Row by clicking the row number and choosing Convert to Footer Row or Convert to Header Row.

Cells in a Header Row or Footer Row are not included in most formulas when you refer to the whole column. So you can use E instead of E2:E5, etc. That simplifies formulas, and the range expands automatically when you add new body rows.

Have a look at the templates at File > New to see how Numbers makes use of multiple tables on a sheet.

SG

Reply

Answer 6

MattRA Author

Level 1

13 points

May 2, 2016 2:09 AM in response to SGIII

Hi SG III

Just reporting back : your suggestions have worked wonders! I’ve got the spreadsheet to do almost everything I wanted.

You opened my eyes to the power of footer and header rows - they’re cool! BTW, what’s the difference between SUMIF and SUMIFS?

One problem I’m having with the location functionality : there is a chance that a location IS listed in the DATA table, but is NOT listed in the SUMMARY table. This means that (as you can see from the sample I’ve set up below), data can go ‘missing” in the SUMMARY table.

Is there a way to ‘catch’ such new entries that list in the DATA table, but not in the SUMMARY table? Even if all the unlisted entires were summed and listed in the SUMMARY table as ‘undefined’ or similar?

Thanks so much for your help so far, it's really appreciated.

Matthew

Reply

Answer 7

SGIII

Level 8

35,258 points

May 2, 2016 6:41 AM in response to MattRA

Hi Matthew

what’s the difference between SUMIF and SUMIFS?

SUMIFS can contain one or more column-condition pairs, so you can test on more than one condition. SUMIF can handle only one.

MattRA wrote:

there is a chance that a location IS listed in the DATA table, but is NOT listed in the SUMMARY table.

You can "refresh" the list of distinct values in the SUMMARY table by using this Copy Distinct Automator Service (Dropbox download).

To install just double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy.

Thereafter to use:

Select the cells with the relevant names (in your example F3:F6 in the DATA table)
Choose Copy Distinct from the Numbers > Services menu
Click once in the destination cell (in your example A2 in the SUMMARY table)
Type command-v or choose Edit > Paste and Match Style to paste.

BTW, you could consider organizing your DATA table something like this:

Note how simple the sum formula in the Footer Row becomes. And it will automatically include new body rows when you add more rows with data.

SG

Reply

Answer 8

MattRA Author

Level 1

13 points

May 5, 2016 3:47 PM in response to SGIII

Hi SGIII

Thanks again for your help. I think that solution, whilst functional, seems very complicated - so maybe I'll just have to be careful to ensure I don't enter new destinations in the DATA table without ensuring they exist in the SUMMARY table. Either that or define a category in SUMMARY as 'unknown' which picks up the difference between actual sales and sales according to listed destinations.

I really like the simplicity that header and footer rows bring - so thank you for introducing me to them! I tried to use a footer row (as in your example above) however I couldn't convert the row from a standard row to a footer row, regardless of what I tried. Could that be because it already contained data and formula?

Matthew

Reply

Answer 9

SGIII

Level 8

35,258 points

May 5, 2016 3:51 PM in response to MattRA

MattRA wrote:

Hi SGIII

I couldn't convert the row from a standard row to a footer row, regardless of what I tried.

Try converting the last row in the table. If there are other (non Footer) rows beneath a row then you can't convert it to Footer.

SG

Reply

Answer 10

MattRA Author

Level 1

13 points

May 5, 2016 4:01 PM in response to SGIII

Aha - that was it! Thanks again.

Reply

Answer 11

SGIII

Level 8

35,258 points

May 5, 2016 4:12 PM in response to MattRA

Glad it's working there now. Footer Rows are convenient. And note you can quickly insert common formulas in them by click a cell in the row and choosing from the fx in the toolbar.

Thanks for the green tick!

SG

Reply