Creation formula with multiple criterias

Hi! I need a formula with an idea simular to that of SUMIF, but I need several "IF" and several criteria for each of them. Let me describe, here is the data:

Name Plant Room Stock
A 2110 1010 1,000
A 2110 2,000
A 3010 3010 3,000
A 2130 2010 4,000
B 2120 1020 5,000
B 2120 2020 6,000
B 2130 7,000
B 2130 1010 8,000
B 2130 2010 9,000

In the other table I want to find out, what is the total stock of A that is on plants 2110, 3010 in rooms 1010,"", 2020. (There are several rooms in each palnt).

so formula must contain this logic: Sum of Stock if Name is A and Plant is 2110 or 3010 and rooms are 1010,"" or 2020.

I found a solution via massive formula, but the real situation is so that there are 500 names, 50 plants and 50 rooms, and I may need up to ten criterias for plant and the same for room, as the data consists of 10000 lines, and I need the outpoot of total stock for each name on certain plants in certain rooms.

In this situation massive megaformula becomes too long to fit in one cell, moreover calculation is too long.

The best result is to create (write in basic or whatever and implant it in excell) a fomula like SUMIF with several possible criterias and ranges of values that fit each of them.

Is it somehow possible?

If the situation is not clear enough, I can provide the massive formula that is the solution for now.

Try something like...

=SUMPRODUCT((A2:A10="A")*(B2:B10={2110,3010})*((C2:C10={1010,2020})+(C2:C10=""))*D2:D10)

Where A2:C10 contains your original data

Note: If your Plant and Room numbers are text entries instead of numerical...then surround those entries in the formula in double quotes.

NBVC gave the correct function. I would use it. nevertheless I would like to show you a way that you can use when the criteria change over time. The way is to use Advanced Filter. I have added a workbook for you to see the actual criteria and the result table. If you put the ActiceCell in the original table and press data->Filter->Advanced Filter, you'll be able to update the result table.

Hope it helps

Multiplying the F&T is easier understood if you first multiply these with 1 or --. Then the TRUE will be one (1) and the False will be 0 (zero). Further you can follow what is happening with the evaluate function. In array functions like Sumproduct this is VERY helpfull.

Check attached...

I created a table of criteria to use in the formula.

You just have to add all your criteria and adjust ranges in formula to satisfy:

Formula used in J2:

Please Login or Register  to view this content.

Note: Checking for the blank entry is kept separate

Please see my last post and attachment.

Not sure what you mean.. can you elaborate?

Originally Posted by s-vadik

Of course!

In the file you have provided the range of criteria for room is set. They are
1010
2020
2130

But also we need a room with noname - "". If we expand the range to one more empty cell, it doesn't work - so you add this part to the final formula
'+($C$2:$C$10="")' to count stock in room "" as well.

It works, but if the situation changes and we no longer need the room "", I can not just change values in CRITERIA range, I must change the final formula to exclude this part.

I want to make it more general - find a way to poot room "" together with all the other criterias in the CRITERIA range and make it work.

One way is to change "" to "-" in the data, but this table is formed by SAP each day, and I'm not sure it would be possible to do it automatically.

I tried to be clear)

Possibly you can use this formula...

Please Login or Register  to view this content.

Where any of the cells in H2:H5 could contain a blank for the Room #

This adds a virtual dash to all entries in C and H so that blanks will seem like dash (-) instead.