Forum Discussion
Help Clean Up This Formula
Hello Caser29,
Use a single SUMIF instead of multiple IF statements.
Formula:
C25-SUM(SUMIF(D3:D24,{"A","B","C"},C3:C24))This subtracts all values in C3:C24 where D3:D24 equals A, B, or C.
For newer Excel (365 / dynamic arrays), you can also use:
C25-SUM(FILTER(C3:C24,ISNUMBER(MATCH(D3:D24,{"A","B","C"},0))))This filters the matching rows first, sums them, and subtracts from C25.
I believe the following should work for you:
=C25-SUM((D1:D24={"A","B","C"})*C1:C24)so to explain what is happening the (D1:D24={"A","B","C"}) creates an array of 24 rows and 3 columns of T/F when the corresponding value in D is equal to the corresponding column for A, B, or C. Then directly multiplying by the C column (which converts the T/F to 1/0) and since each row will either have 1 true value or none since any 1 value cannot be = to more than 1 of those options A, B, or C then the product with column C will either have 1 value from column C or all values of 0. Then just sum the entire grid and subtract that from C25. The value in all of column C must be numbers or you will get an error.