Array of conditions in SumIf with criteria in different columns-CodePudding

I got a simple data table and wanted to sum up the values only if two criterias are both matched, which are located in 2 different columns of each table row.

Summing up the values for only 1 condition (month OR application) works well:

=SUMMEWENN($C$2:$E$16;H2;$E$2:$E$16)

Result: token usage for different months

Now i wanted to break down the monthly usage into the different products. E.g. the token usage for the product "AutoCAD" in the month "Jänner". These approaches i´ve tried:

=SUMMEWENN($C$2:$E$16;AND(H2;D2);$E$2:$E$16)
=SUMMEWENN($C$2:$E$16;AND("Jänner";"AutoCAD");$E$2:$E$16)

Result: 0 (every value == 0)

=SUMMEWENN($C$2:$E$16;{H2;D2};$E$2:$E$16)
=SUMMEWENN($C$2:$E$16;{H2,D2};$E$2:$E$16)

Result: syntax failure

=SUMMEWENN($C$2:$E$16;H2;$E$2:$E$16) SUMMEWENN($C$2:$E$16;D2;$E$2:$E$16)

Result: 26 (false, summing up separately from each other)

Desired result:

How can i reach the desired result with inbuild excel functions?
(I only want to set up a VBA script if it is absolutely impossible to do otherwise).

CodePudding user response：

You can use SUMIFS for adding multiple conditions should apply for the set you want to sum a range. For example:

=SUMIFS($F$2:$F$16,$C$2:$C$16,H2, $D$2:$D$16, "AutoCAD")