Originally Posted by
jhendrix
...If I can get around adding another column, I would prefer to.
you can, but as outlined already it makes the process more "expensive" ...
generally speaking utilising helpers mean more efficient formulae.... in this case you'd even be able to use a Pivot Table.
You can create a named range:
In the Named range I'm assuming A only ever contains text values.
Then
The above works given the 1:1 relationship between Staff/Case/Status
(ie a Case only belongs to 1 Staff member (seemingly) and has only 1 status (seemingly))
Insert a PT with Range defined as _Data, subsequently setting Staff as Row Label and Unique as Data Field (set to SUM)
Sorting the data as Teylyn has already said would really aid efficiency in terms of the above process given you could dispense with COUNTIF - but I've disregarded given you've already made mention this is not viable.
In terms of conducting the calcs without the use of the additional column whilst still utilising the Named Range then again taking advantage of the 1:1 relationships:
but the above will perform poorly...
As a final option - if you were happy to keep the helper column but did not want to use a Pivot Table you could just use a SUMIF in place of the above which would be significantly quicker:
EDIT: the above SUMIF is in essence just a reworking of Teylyn's earlier point re: use of COUNTIFS but working off later assertion that data can not be sorted...
If adopting the helper but no PT approach you could still use COUNTIFS and replace the COUNTIF calc in the helper col. with a a basic MATCH approach which might be quicker pending volume ?
Bookmarks