SUMPRODUCT or product within SUMIF with multiple criterion and IF statements.

[n8te]

Hi there,

While I'm quite able to write some decent code in VBA, I do not really know why but I still meet difficulties coding cells formula.

Here is a SUMIF formula I have:

=SUMIFS(INDIRECT("tableA["var" & LEFT(narea1;FIND(" ";narea1) -1) &"]");   
tableA[P_ID];IF([@P_ID]="all";"<>";MATCH([@P];tablec[varname];0));
tableA[T_ID];IF([@T_ID]="all";"<>";VLOOKUP([@T_ID];narea2;2));tableA[Y_ID];
IF([@Y_ID]="all";"<>";[@Y_ID]))

Basically, in table A, I want my formula to sum conditionally amounts in table b based on info retrievable from table A, C or 2 different named areas. This formula works such fine.
I realized however that I made a theoretical mistake and I want to weight the amounts in table b with an another variable in the same table with the same condition.

I think then that I need a sumproduct function but I do not understand how to integrate the different criteria in it. Anyone has an idea how to adapt this formula to a sumproduct ?

 

[n8te]

I made some improvements and I found a way to get the sumproduct. However I still having some trouble with the nested If statements.

Here is my new formula:

=SUMPRODUCT(INDIRECT("tableA[var & LEFT(a_cost;FIND(" "narea1) -1) &"]");(tableA[P_ID]=IF([@P_ID]="all";;MATCH([@P_ID];tablec[varname];0)))*(tableA[T_ID]=IF([@T_ID]="all";;VLOOKUP([@T_ID];narea2e;2)))*(tableA[Y_ID]=IF([@Y_ID]="all";;[@Y_ID])))

I want the formula to sumproduct variables in a table A (for now, only one array is specified) based on conditions based on table B. As you can see on the screenshot herebelow, the formula only works when there is not one condition that contains the "All" item.

I don't know how to specify in the IF statement that there should be no conditions when the "ALL" statements appear.

I still don't know if my request is understandable :/