Excel SUMIF formula for cells that fall in column has "Total" and row has "Commissionable" and and row has Non-Commissionable

Question

I'm looking for some help in writing a SUMIF formula or a better alternative. The idea is the Grand Total in cell I20 will automatically

sum up cells that intersect rows with Non-Commissionable in it and columns with Total in it

and

sum up cells that intersect rows with Commissionable in it and columns with Total in it

The use case for this formula is important bc the position of rows or columns can and do change

---

Continuation Question to below commented solution

With this application, can I please get an understanding of why this is not applicable or not catching beyond the first match.

Answer 1

(1) Since it looks like you have Office 365, try this simplified formula:

=SUMIFS(XLOOKUP("Total", C4:I4, C5:I18), B5:B18, "*Commissionable")

The XLOOKUP function will return the range of the first column found where the column header is equal to "Total".

The SUMIFS function will then sum anything in the Total column, where the value in the Description column ends with "Commissionable" ("*Commissionable"), which will include both "Commissionable" and "Non-Commissionable".

(2) The XLOOKUP function can also perform a wildcard character search to find the column header that ends with "Total" ("*Total") or contains "Total" ("*Total*"). To do this, you must set the optional [match_mode] parameter to 2 (Wildcard character match):

=SUMIFS(XLOOKUP("*Total*", C4:I4, C5:I18,, 2), B5:B18, "*Commissionable")

This method will successfully find "Total" within column headers labelled "Total Sales", "Subtotal" or "Grand Total", for example, but it will still ONLY return the FIRST match found.

(3) If your data set has TWO "Total" columns, and you want to include BOTH in the Grand Total, you could add a second SUMIFS/XLOOKUP combo with the optional [search_mode] parameter set to -1 (Search last-to-first):

=SUMIFS(XLOOKUP("Total", C4:I4, C5:I18), B5:B18, "*Commissionable")
   +SUMIFS(XLOOKUP("Total", C4:I4, C5:I18,,, -1), B5:B18, "*Commissionable")

The first XLOOKUP will find the first "Total" column searching from left-to-right, and the second XLOOKUP will find the first "Total" column searching from right-to-left. NOTE: this method ONLY works with TWO "Total" columns.

(4) If you need to return multiple matches both vertically and horizontally (rows and columns), you could use either the SUMPRODUCT function, or SUM with nested FILTER functions:

=SUMPRODUCT(C5:I18 * ((B5:B18="Commissionable")+(B5:B18="Non-Commissionable")) * (C4:I4="Total"))

OR

=SUM(FILTER(FILTER(C5:I18, (B5:B18="Commissionable")+(B5:B18="Non-Commissionable")), C4:I4="Total"))

Both of these methods are virtually identical in their logic, and neither one will accept wildcard characters. Multiple conditions using AND logic are multiplied together (*) and multiple conditions using OR logic are added together (+). In plain English, the above SUMPRODUCT formula can be read as:

Sum all cells in range C5:I18 where the values in range B5:B18 are equal to "Commissionable" OR "Non-Commissionable", AND the values in range C4:I4 are equal to "Total".

(5) BONUS method to use wildcard characters with the FILTER function, by leveraging COUNTIF with the new BYROW function:

=SUM(FILTER(FILTER(C5:I18, BYROW(B5:B18, LAMBDA(r, COUNTIF(r, "*Commissionable")))), C4:I4="Total"))

The inner FILTER function returns the rows from range C5:I18 where the include conditions are met. In this case, the BYROW function loops through range B5:B18, row by row, and returns either 0 (FALSE) or 1 (TRUE) if the value in each row ends with "Commissionable". The resulting filtered rows are then filtered again by the outer FILTER function, this time returning the columns where the values in range C4:I4 are equal to "Total". The final results are then summed up by the SUM function.