|   A   | B|    C  |   D    |   E  |   F  |   G  |H|    I    |     J    |    K     |  L
---|-------|--|-------|--------|------|------|------|-|---------|----------|----------|-----
   |       |  | ratio | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
2  |Brand A|P1|   7%  |   yes  |  500 |   70 |   60 | |   2024  |  Brand A |    P1    |         
3  |Brand A|P2|   8%  |   yes  |  100 |   47 |  300 | |         |  Brand B |          |            
4  |Brand A|P2|  10%  |   yes  |  800 |   21 |  200 | |         |          |          |          
5  |Brand B|P1|   5%  |   yes  |   90 |   56 |  150 | |         |          |          |                     
6  |Brand C|P4|   3%  |   no   |   45 |  700 |  790 | |         |          |          |                  
7  |Brand C|P2|   8%  |   no   |  600 |  150 |   40 | |  Result |    39.5  |          |          
8  |Brand D|P1|  12%  |   yes  |  900 |   90 |  980 | |         |          |          |                    
9  |Brand D|P1|  20%  |   yes  |  125 |  854 |  726 | |         |          |          |          
10 |Brand D|P3|  19%  |   yes  |   70 |  860 |  614 | |         |          |          |          
11 |Brand D|P3|   2%  |   yes  |  842 |  250 |   85 | |         |          |          |          
12 |Brand E|P4|   6%  |   no   |  300 |  324 |  450 | |         |          |          |   

   

In Cell J7 I want to calculate the SUMPRODUCT based on multiple column criteria and a unique row criteria:

1. The criteria for the row you can find in Cell I2
2. The criteria for the columns you can find in Range J2:J3 and Range K2:K3.

In the example above the result is:

J7 = 500x7% + 90x5% = 39.5

So far I have been able to extract the SUM based on the criteria using this formula:

=SUM(IF(COUNTIF(I2;C1:G1)*COUNTIF(J2:J3;A2:A12)*COUNTIF(K2:K3;B2:B12);C2:G12))

However, I have no clue how to change this formula so it calculates the SUMPRODUCT and includes all the criteria both for column and row?