Search code examples
excelsumifssumproduct

SUMPRODUCT is taking way too long

I have this data:-

enter image description here

This is what I want to achieve:-

enter image description here

By selecting the customer as "ABC", the respective amount will show at Jan to Jun.

I can achieve this by using SUMPRODUCT but it takes a long time to process as my data is more than 10K rows. Is there a more efficient way to do this using SUMIFS etc?

Thank You

Solution

  • Here is a function that you can paste to L2 in your example and copy down to December.

    =SUMIF(INDEX(Data,0,1),$L$1,INDEX(Data,0,MATCH(K2,Months,0)))

    As you see, it has 2 named ranges, Data and Months.

    1. Data comprises of all data rows and columns, A1:G9 in your posted example. I think you will want to make this range dynamic, meaning it should adjust in size automatically as you add or delete data. One can do this is the formula but that isn't efficient. Therefore you do it in the name declaration. If you need help with that please don't ask here. It's a different subject: "dynamic named ranges".
    2. Months should have as many columns as 'Data', not limited to 12. It's important that it should start in the same column as Data and that its first field shouldn't hold a date that might become a search criterium of the function.

    Column K can also have more than 12 columns. In my setup I used a custom number format mmm. Therefore 1/1/21 and 1/1/22 will be displayed identically but the MATCHG function can tell them apart.

    I placed a start date into B1 and =EDATE(B1,1) into C1, which I copied to the right. Similarly, K2 has =B1, K3 =EDATE(K2,1) which is copied down. This ensures that column and row captions are aligned.