Search code examples
excel-formulasumifs

Simple and efficient IFS and SUMIFS

I have a sample attached data and I want to use SUMIFS to evaluate the data

enter image description here

For example, given the following criteria and multiple conditions

Class: buz
Centre: tre
Unit: a
Section: x

I can easily write a sumifs like so : 

=SUMIFS(Amount,Class,buz,Centre,tre,Unit,a,Section,x)

However if all values of one or two ranges need to be evaluated and not just one of it like the above, (e.g I want all classes and not just buz) then I can combine if with sumifs

Conditions:
Class: all classes in the table
Centre: tre
Unit: a
Section: x 

=If(Class=all classes in a range,   
SUMIFS(Amount,Centre,tre,Unit,a,Section,x),
SUMIFS(Amount,Class,buz,Centre,tre,Unit,a,Section,x))

I'm looking for a better way to achieve the above that will be simpler and especially be fast and more efficient as SUMIFS tend to be slow with large datasets.

Solution

  • I think using a * character instead of the class would achieve what you want (return all classes) without changing the formula.

    If speed is a concern, VBA can also give you faster solutions, especially if you want to compute this total for a lot of categories.