[SOLVED] Calculate SUMPRODUCT with multiple column criteria and one row criteria

Calculate SUMPRODUCT with multiple column criteria and one row criteria

	A	B	C	D	E	F	G	I	J	K
			ratio	tested	2023	2024	2025	RowCrit	ColCrit1	ColCrit2
2	Brand A	P1	7%	yes	500	70	60	2023	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 one row criteria:

The criteria for the row you can find in Cell I2
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?

Solution

Try:

=LET(
    a, COUNTIF($J$2:$J$3, $A$2:$A$12),
    b, COUNTIF($K$2, $B$2:$B$12),
    c, CHOOSECOLS($A$2:$G$12, 3, XMATCH($I$2, $A$1:$G$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)

To account for no entries in ColCrit1, or no entries in ColCrit2, you could modify it:

=LET(
    a, IF(
        COUNTA($J$2:$J$5) = 0,
        N(ISNUMBER(ROW($A$2:$A$12))),
        COUNTIF($J$2:$J$5, $A$2:$A$12)
    ),
    b, IF(
        COUNTA($K$2:$K$5) = 0,
        N(ISNUMBER(ROW($B$2:$B$12))),
        COUNTIF($K$2:$K$5, $B$2:$B$12)
    ),
    c, CHOOSECOLS($A$2:$G$12, 3, XMATCH($I$2, $A$1:$G$1)),
    SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)