Simpler solutions than sumifs

As you are no doubt aware the layout of your data precludes efficient / straightforward approaches.

You don't stipulate your existing SUMIFS approach but in theory it should look like:

Please Login or Register  to view this content.

In short, 4 SUMIFS per Result cell calculation (one for each Activity Group on Data)

(note: your summary file includes some erroneous entries... ie activity balances for which there is either a missing Job ID or missing Activity)

The above though not ideal is not horrendous performance wise... reducing the SUMIFS to one per Activity would necessitate a Job ID reference per Activity reference (ie 4 Job ID references) else including the Job ID within the Activity Reference (eg 123@Activity A)

As DO says, a better constructed sheet would make things more efficient

DO - Thanks for your reply. However there are four SUMIFS for just one group and I have five such groups. I have already done this through SUMIFS. Attached is the real sheet with dummy data. I am recording the performance of my people in a particular task. Now one person may perform that kind of task on five different co.'s in a single day. All the co's may not involve all the activities. Example: for company 1 one agent may do activity 1 and activity 2. Qc will be done by another agent. That is the reason I have such a huge sheet. Please suggest any better way to record data or any better formula.

Roy- It will really help if you can also suggest a better format.

Please note that these two sheets are just a part of another report. If you can give me a better way to restructure this, I may probably change the whole report.

Thanks in advance...

Cheers-
Inayat

Inayat,

I think perhaps you missed the significance of my prior post, namely: given the recurring pattern of your Group "tables" you need only one SUMIFS per Activity - so 5 SUMIFS for each result and not 20.

To be clear, the below applied to your matrix (E3:I52) will generate exactly the same results as you have presently in your sample. As you can see there are significantly fewer SUMIFS than before.

Please Login or Register  to view this content.

As mentioned it comes down to the recurring pattern of your tables.

To use a simplistic example...:

4 columns of data
A & C hold a text value of either "X" or "Y"
B & D hold various numeric values

We want to get the sum of all "X" values but rather than use:

Please Login or Register  to view this content.

we use:

Please Login or Register  to view this content.

The SUMIF will look for "X" in each used cell within A:C range and where found include the adjacent value in the optional range to sum. This is viable because we know "X" will not appear in column B (and furthermore we assume C does not hold numerics)

edit:
It is also important to note that each range within the SUMIF shares the same dimension (explicitly or otherwise) - this is why we end up with 4 SUMIFS in your case as we can not have a summation range whose dimensions exceed that of the criteria (1 column)

The need for 4 SUMIFS results from the dimension equality requirement.

You have 2 sets of criteria, Activity & Job ID and whilst the dimensions of Activity match those of the summation range the dimensions of Job ID do not - the latter being just 1 column wide per table

The result of the above is that the summation column can only ever be 1 column wide and you must thus handle each summation/activity column as a separate SUMIFS.

This goes back to the point I made in my first post:

Originally Posted by D.O

...reducing the SUMIFS to one per Activity would necessitate a Job ID reference per Activity reference (ie 4 Job ID references) else including the Job ID within the Activity Reference (eg 123@Activity A)

Such that your criteria dimensions match those of the summation thereby permitting a single SUMIFS function.

You should persist with SUMIFS. If you wish to make the formulae more succinct you should adapt your layout accordingly.