Forum Discussion
Help Clean Up This Formula
Hello,
I am looking for a way to compact this formula more. I have about 20 rows I need to do.
=C25-IF(OR(COUNTIF(D3,{"A","B","C"})),C3,0)-IF(OR(COUNTIF(D4,{"A","B","C"})),C4,0)-IF(OR(COUNTIF(D5,{"A","B","C"})),C5,0)
I am trying to minus the amounts in column C# if Column D# = conditions A, B or C from C25. The math is all happening in one cell. Both columns are 3 through 24.
I am hoping there is a way or am I SOL and have to do each row by hand?
Thank you,
Casey
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.
2 Replies
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.
