How to sum up specific things in Numbers

iworknumbers

I have a spreadsheet in the newest iWork Numbers.app, with something like:

CATEGORY | POINTS  
      HW | 100
      HW | 100
      NW | 12
      HW | 50
      NW | 23

I want to just sum up all the Category = HW into a cell: I'm not sure how. I think I need to use the SUMIF() function but I get stuck afterwards.

Best Answer

You are correct in that you need to use the SUMIF function. This is the specification from the Function Browser:

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

Try this in your cell:

=SUMIF(CATEGORY; "=HW"; POINTS)

Related Solutions

Can Numbers do conditional coloring of a cell based on a rule

Here's a better option:

Add an 'Account Status' column
Select the entire column and set the cell format to [Pop-Up Menu] with options for 'Unfinished', 'Paid', '30 Days', '60 Days', '90+ Days' (or whatever options you want)
Select the entire column and set some conditional rules to color the cell according to the text in the cell (e.g. a green for [Text Is 'Paid'], a yellow for [Text Is '30 Days'], etc.)
Add some summary cells with COUNTIF formulas for each of your Pop-Up menu options ([=COUNTIF(Account Status,"Paid")], [=COUNTIF(Account Status,"30 Days")], etc.)

Note that this will color ONLY the cells in the 'Account Status' column. There are some tricks to color the entire row, but this should be enough to catch your eye.

You can also use AppleScript if you need more complex summaries, but scripts cannot run automatically, where conditional rules do.

How to handle Numbers’ automatic Data Formatting

Without access to the spreadsheet that you've shown, I've attempted to recreate this myself. It appears that there are two things wrong with the spreadsheet above:

The test-array for the countif is of Data Format Automatic. This means that the zeros are not of format Duration and can't be compared by the countif.

To fix this, select the entire array and choose Duration from the Data Format list.
The countif's condition is of format Number whereas the test-array is of format Duration.

To fix this, use the following for the countif cell:
```
=COUNTIF(G2:G11,">0h 0m")
```

Best Answer

Related Solutions

Can Numbers do conditional coloring of a cell based on a rule

How to handle Numbers’ automatic Data Formatting

Related Question