I'm trying to sum over multiple columns where the row criteria can be one of multiple things. I tried sum(filter(filter( with an or function, but that didn't work. You can see the formula below. For example, in a workbook with semi-annual columns, B:E, I want to add up the values where the year (row 1) is 2020 and business code (column A) is 4142 or 4143.

SUM(FILTER(FILTER($B$3:$E$2965,$B$1:$E$1=2020),OR($A$3:$A$2965=4142,$A$3:$A$2965=4143)))

CodePudding user response：

=SUMPRODUCT(--($B$1:$E$1=2020)*(($A$3:$A$2965=4142) ($A$3:$A$2965=4143)),$B$3:$E$2965)

Using the * acts as an AND function, and the acts as an OR function. The -- will convert the boolean values to 1s and 0s, which then gets multiplied across the data range.