SUMIFS not working properly

[naveen73]

Hi All,I have a big file with ratio and amounts in column. From this I want to calculate the weighted average of a ratio. However, all the ratios and amounts are in the same column. They are for several years and for each year several scenarios and for each scenario several balance items for several banks. What I want is in a separate new column (on the right) a ratio for one specific year a specific balance sheet item for a specific scenario for all banks. This is the approach I have taken.I am using the following formulas in excel:=SUMIFS(E:E,A:A,A2,B:B,B2,C:C,$G$1,D:D,D2)Let me explain the formula a bit:Sum everything in column E IF:• A:A,A2 ; in column A all the bank_code, so for each bank• B:B,B2 ; in column B the period/year are mentioned, for each year• C:C,$G$1 : in column C the balance sheet items are mentioned, the specific balance I am looking for is in cell $G$1• D:D,D2 ; in column D all the scenarios are mentioned so for each specific scenario Why am I using the SUMIF function? That is my way of filtering things out. I want to do the same for the amounts in the next column.After making the two columns I want to use the sumproduct function with a conditional products calculation.My problem is that for some row the abovementioned SUMIF formula is not working. Why is that is it not working for some rows and some not?Just started off with DAX. Is there a better way of doing this in DAX? I will be using the end results to upload to Power BI / Tableau or Qlikview.Thanks,Naveen