Hello @Darren_Murphy , exactly, it’ll be for each user. Each user has a column in a separate sheet with the name, email and company name. I’ll filter by company name, because some users share orders (same company, different team member)
Thanks in advance!
One column per user, or one row per user?
I’ll assume that is a typo and you actually have one row per user. If you really have one column per user, then you’ll be wanting to change that - otherwise you’re in for a world of pain
Anyway, there are a few different ways you could approach this. I’ll describe one way that’s fairly simple. I’ll assume that your orders table has one row per order, and columns that identify both the user the order belongs to, and the order status.
Firstly, in your Orders table, create two if-then-else columns as follows:
– If Status is Completed, then true
– If Status is Pending, then true
Now in your Users table, create a multiple relation column that matches the user identifier (email, UserID, whatever) with the user identifier in your Orders Table
Now create 3 rollup columns:
– The first will take a count of the user identifier through the relation column (this will be your Total)
– The second will count the number of true values in the first if-then-else (Completed) column
– The third will count the number of true values in the second if-then-else (Pending) column
And those 3 rollup columns can be used for your Big Numbers
