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

Which function would be used to count numbers by series in a column

I track the status of library books in Numbers. Books out on loan are number in the 100s (the first book lent out is '100', the second '101', etc.) Each status has a series.


100=Checked Out

200=Ready for Pick Up

300=Requested

400=Revisit

500=Pursue
600=Unavailable

700=Canceled

800=Returned


For reporting purposes, I would like to add a header row to show a status count (the number of 100-series, 200-series, etc.)


I've looked at CountIf, with a text array of A5:A7000. The problem is with the condition syntax. Ikeep getting a syntax error when I put '=100 or <200' as a condition. I thought I'd be able to put that in each of the cells in which I need the counts, incrementing the syntax accordingly ('=200 or <300' for the 200 Series count, and so on.)


I would appreciate input on this, especially if CountIf is appropriate to what I'm trying to count, and how to properly format the syntax to get the counts I'm looking for.


Here is a sample of my sheet:


STATUS

LOCATION

100

Carver

101

River Center

102

Carver

200

Carver

201

River Center

203

Gonzales

300

ASC-ILL

301

River Center

302

River Center

303

River Center

400

STL

500

River Center

501

STL

iMac, OS X Mountain Lion (10.8.5)

Posted on Nov 16, 2013 5:32 PM

Reply
Question marked as Best reply

Posted on Nov 16, 2013 5:54 PM

Hi Darrell,


Here's an example using COUNTIF. I've moved the count to a separate table. Table names are shown above each table:

User uploaded file

Formula:


Summary::B2, and filled down to B6: =COUNTIF(Main :: $A,"<"&A3)-COUNTIF(Main :: $A,"<"&A2)


Note that A7 must contain a numberlrger than the starting number for the last category you are recording.

B7 should be left empty.


Row 1 on each table is a Header Row.


Regards,

Barry

3 replies
Question marked as Best reply

Nov 16, 2013 5:54 PM in response to Darrell1405

Hi Darrell,


Here's an example using COUNTIF. I've moved the count to a separate table. Table names are shown above each table:

User uploaded file

Formula:


Summary::B2, and filled down to B6: =COUNTIF(Main :: $A,"<"&A3)-COUNTIF(Main :: $A,"<"&A2)


Note that A7 must contain a numberlrger than the starting number for the last category you are recording.

B7 should be left empty.


Row 1 on each table is a Header Row.


Regards,

Barry

Which function would be used to count numbers by series in a column

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.