EN VI

Excel formula problem with sumifs with multiple search values in one if?

How to Excel formula problem with sumifs with multiple search values in one if

I am using sumifs to identify in a data table cost entries using "cost positions". These have typically numbers such as 100, 200 etc. But the intermediate numbers 101, 102 also exist but must be neglected.

Currently I have the search number in a cell named "position" and search for my data using the table name Cost with entry Position: "Cost[Position]"

=SUMMEWENNS(Cost[Wert/BWähr];Cost[Position];Position)

This works for a single value, but I want to search for a list of values, such as "100, 200, 400, 500". But I do not want to add an if entry for each value, since I never know if it is 1, 2 or 10 values.

Is this possible?

The dumb way of doing this would be to add all the values

=SUMMEWENNS(Cost[Wert/BWähr];Cost[Position];Position1)
 +SUMMEWENNS(Cost[Wert/BWähr];Cost[Position];Position2)

Solution:

If I have understood correctly, from your OP then you would need a formula like this:

• Formula used in cell E5

=SUM(SUMIFS(D5:D24,C5:C24,SEQUENCE(ROWS(UNIQUE(C5:C24)),,100,100)))

In German Version of Excel it will be:

=SUMME(SUMMEWENNS(D5:D24;C5:C24;SEQUENCE(ZEILEN(UNIQUE(C5:C24));;100;100)))

For your data in context it will be:

=SUMME(SUMMEWENNS(Cost[Wert/BWähr];Cost[Position];SEQUENCE(ZEILEN(UNIQUE(Cost[Position]));;100;100)))

So how does this formula works:

Using UNIQUE() function extracting the unique positions.
Using ROW() function getting the number of unique rows.
Using SEQUENCE() function getting the positions sequence starting from 100 and incrementing each by 100 as well ( The reason behind to use this because its clearly mentioned in your post --> These have typically numbers such as 100, 200 etc. <--. and hence creating the same sequence we can escape the intermediate numbers).
Using the above as criteria in SUMIFS() function, while the rest follows as per the syntax of the said function.
Lastly, wrapping within, SUM() function to get the total of the values returned in the array.

=LET(α, Cost[Position], SUM(SUMIFS(Cost[Wert/BWähr],α,SEQUENCE(ROWS(UNIQUE(α)),,100,100))))