Counting cells based on values

I have some data that looks a bit like this;

ID	Value
a	1
b	4
c	2
d	1
e	3
f	2
g	2
h	1

i'd like to calculate the number of ID's with a Value of 1 or more (all of them), 2 or more (5), 3 or more (2) and 4 or more (1). It seems like it should be fairly straightforward but I'm struggling a little. I'm trying to use the summarise tool.

Thanks in advance for any help you can offer.

Preparation

Accepted answers

DataBlender

Hi @DHB

The most direct way would be to put 4 filters in for each of your thresholds and then a summarize tool after which counts the number of IDs coming out of the true output.

I packaged this approach up into a batch macro so you can enter a list of thresholds as another input to save repeating the tools four times.

If anybody else has another way of doing it without a macro I'd be interested to see it!

Filter Batch.yxzp

All comments

DataBlender

Hi @DHB

The most direct way would be to put 4 filters in for each of your thresholds and then a summarize tool after which counts the number of IDs coming out of the true output.

I packaged this approach up into a batch macro so you can enter a list of thresholds as another input to save repeating the tools four times.

If anybody else has another way of doing it without a macro I'd be interested to see it!

Filter Batch.yxzp

DHB

Thank you for your solution Bolide. It worked perfectly.

JohnJPS

A non-macro approach would be: (1) sort on value descending, (2) apply record ID, (3) Summarize grouping by value and selecting max(RecordID), which hopefully will be the count you're after:

SimpleRunningTotalFrequency.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703