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

Question:

Question: How to create Subtotal in Numbers

No one at Apple Store training could tell how to create a subtotal in numbers. Example: Range of customers created revenue , at year end how to create subtotal revenue by customers.
Or Category Expense by IRS Code, at year end subtotal expenses by IRS Code.

Do I use SUMIF? What Formula?

Mac OS X (10.7)

Posted on

Reply
Question marked as Solved
Answer:
Answer:

"I replicated your example to the letter"

If that were true, you would see the same results in E2, E3 and E4 as shown in Badunit's example. there would be no error message in D2 as D2 does not contain a formula, and error messages occur only in cells where there is an error in the writing or the working of that formula.


Examine your version of the example closely and compare it cell by cell to the example. Correct the differences, and you should see the same results as shown in the example.


The syntax for SUMIF is this:


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


test-values: the range of cells whose values are to be compared with 'condition.'
In the example, all non-header cells in column A, expressed in the formula as A

condition, include only the values that meet this condition

In the example, "=" (implied) the value in cell D2

sum-values the range of cells whose values are to be included in the sum (if the value in their row of column A

meets the 'condition'.

In the example, values in column B, where the same row of column A contains the same value as D2.


Numbers, in any version, is no more frustrating than any spreadsheet application with respect to this function. Take a closer look, work your way through it, and you'll succeed.


Regards,

Barry

Posted on

Question marked as Helpful

Feb 28, 2018 12:05 PM in response to udor46 In response to udor46

"I used pop up menu to have consistent data category."


Good move.


Regards,

Barry


PS: Badunit's second post (the one with the table and formula) contained the actual solution to your issue. Since you've already used yur "Solved" mark, please mark that post "Helpful" to bring it to the top section, where it will be easier to find by someone coming later with a smilar question.

B.

There’s more to the conversation

Read all replies

Feb 27, 2018 9:38 AM in response to Badunit In response to Badunit

Column A. IRS CODE ———- Column B AMOUNT

Shipping $10

Communication. $50

Travel. $100

Shipping. $45

Travel $250

Etc.

Subtotal YEAR END by IRS CODE Category

Same goes by Income

CUSTOMER NAME. AMOUNT

Smith. $500

Jones. $200

Smith. $300

etc.

Subtotal YEAR END by CUSTOMER



Thanks.

Feb 27, 2018 9:38 AM

Reply Helpful

Feb 27, 2018 3:12 PM in response to Athanti In response to Athanti

Did not help as a I am a Numbers novice and need a step by step guidance.

Again, I would like a subtotal of each category and tried the SUMIF without success. Syntax error.

Thanks everyone.




CATEGORIES

AMOUNT

Category1

23

Category2

18

Category 3

5

Category1

98

Category 3

6

Category1

19

Category2

101


Feb 27, 2018 3:12 PM

Reply Helpful

Feb 27, 2018 3:30 PM in response to udor46 In response to udor46

User uploaded file


Fill down cell E2 to create E3 and E4. Be careful that the "categories" in column A are consistent in their spelling, "Category3" is not the same as "Category 3". If you are not careful, you might not sum everything you meant to sum. One way to be careful is to use a pop up menu for the cells in column A. This forces you to choose from a list of "categories" vs typing it in each time. This advice may or may not apply to your situation.


Instead of "A" and "B" which reference the entire columns A and B, you can use ranges such as A3:A9 and B3:B9.


When you type the "=" to start a formula, you can look up function(s) in the sidebar on the right to see what is required to complete the function(s).

Feb 27, 2018 3:30 PM

Reply Helpful

Feb 27, 2018 5:04 PM in response to Badunit In response to Badunit

Thanks,


I replicated your example to the letter but all I receive in Cell D2 is:::

'This formula can’t reference its own cell, or depend on another formula that references this cell."

Numbers version 4.3.1 is very frustrating.


Oh well, almost giving up on such a simple calculation.

Feb 27, 2018 5:04 PM

Reply Helpful

Feb 27, 2018 5:09 PM in response to udor46 In response to udor46

There are no formulas in column D. I typed the three category names by hand. The formulas are in column E. The formula shown is in cell E2


Maybe the highlighting threw you off? With the cell selected (cell E2), Numbers highlights all of the cells and ranges being referenced by the formula. That would be column A, column B, and cell D2.

Feb 27, 2018 5:09 PM

Reply Helpful
Question marked as Solved

Feb 28, 2018 12:05 PM in response to udor46 In response to udor46

"I replicated your example to the letter"

If that were true, you would see the same results in E2, E3 and E4 as shown in Badunit's example. there would be no error message in D2 as D2 does not contain a formula, and error messages occur only in cells where there is an error in the writing or the working of that formula.


Examine your version of the example closely and compare it cell by cell to the example. Correct the differences, and you should see the same results as shown in the example.


The syntax for SUMIF is this:


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


test-values: the range of cells whose values are to be compared with 'condition.'
In the example, all non-header cells in column A, expressed in the formula as A

condition, include only the values that meet this condition

In the example, "=" (implied) the value in cell D2

sum-values the range of cells whose values are to be included in the sum (if the value in their row of column A

meets the 'condition'.

In the example, values in column B, where the same row of column A contains the same value as D2.


Numbers, in any version, is no more frustrating than any spreadsheet application with respect to this function. Take a closer look, work your way through it, and you'll succeed.


Regards,

Barry

Feb 28, 2018 12:05 PM

Reply Helpful (1)
Question marked as Helpful

Feb 28, 2018 12:05 PM in response to udor46 In response to udor46

"I used pop up menu to have consistent data category."


Good move.


Regards,

Barry


PS: Badunit's second post (the one with the table and formula) contained the actual solution to your issue. Since you've already used yur "Solved" mark, please mark that post "Helpful" to bring it to the top section, where it will be easier to find by someone coming later with a smilar question.

B.

Feb 28, 2018 12:05 PM

Reply Helpful (1)
User profile for user: udor46

Question: How to create Subtotal in Numbers