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.
Bookmarks