Summarize (count) a numeric field with a condition?

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered

Hello,

I have a field that gives a "0" (false) or "5" (true) for "if meets goal".

I would now like to be able to summarize how many of the "0" and "5" there are for a specific group or Service Area.  For example, the main field is Service Area and the Service Area can have tens to hundreds of rows of data (see below):

Aquatics   Swim class 1    0

Aquatics   Swim class 2    5 

etc...

I have tried creating s summary report but it will only total the 0 and 5's.  I then tried to "define a calculated column" but can seem to find a formula that will work (I am a novice at this...).  What I want to show is how many "0" there are and how many "5" there are by Service Area (maybe by total AND percent) and maybe even graph this to show managers.  I am trying to show a manager how many courses "meet" the goal and how many "don't meet" the goal.

Any help would be great!

Thanks.

Photo of MJ Hickey

MJ Hickey

  • 0 Points

Posted 4 years ago

  • 0
  • 1
No problem.
Make a field called [# of 0's] with a formula of
If([Rating]=0,1,0)


Make a field called [# of 5's] with a formula of
If([Rating]=5,1,0)

Then do a summary report and calculate the total of those fields on the report.
Photo of Leith Simpson

Leith Simpson

  • 140 Points 100 badge 2x thumb
What if in general I want to do a "count" summary where there are indefinite numbers of options. For example, I want to summarize events by the date they occurred, and each event was completed by a user, and I want a summary of the number of users who created events that day.

Similarly, each event is related to an object in another table, and I'd like a summary of the number of objects associated with events each day.

The "# of events" field is automatic but I wish I could get a "count" of any numeric field.

Thanks,
I suggest that you post this is a new question and describe your tables and their relationships.

This conversation is no longer open for comments or replies.