Trying to convert an Excel formula to work with Google Sheets

Question

I have a formula in Excel. On a filtered table, It will check in column B for a keyword match, and then subtotal the values in the corresponding column C.

=SUMPRODUCT(SUBTOTAL(109,OFFSET(E16,ROW(E16:E100)-ROW(E16),,1)),--(B16:B100 = "Sell"))

The same formula isn't supported in Google Sheets and I haven't quite managed to figure out how to get it working. Any ideas please?

The error I am seeing is

SUMPRODUCT has mismatched range sizes. Expected row count: 1, column count: 1. Actual row count: 85, column count: 1.

Example:

There is a filter on the Items column. In this case "Box". I need the formula to Check if Type is "Sell". there can be multiple types. Need to subtotal the values in the price column for all rows as sell. Expected output in this case would be subtotal 11 for Sell and 3 for Buy.

Answer 1

Using SUBTOTAL

In Excel you can reference a range with OFFSET and pass it to the SUBTOTAL function but in Google Sheet this tecnique won't work in the same way.

In order to obtain a dynamic SUBTOTAL formula that updates with filters or hidden columns you will have to create an additional column: Subtotal_Helper. In this column you will use SUBTOTAL like this:

|     E     |          F          |
+ - Price - + - Subtotal-Helper - +
|     3     |  =SUBTOTAL(109, E2) |

Basically this column will contain the Price value if visible 0 otherwise.

Now you can combine this logic with any condition you need to build a dynamic SUBTOTAL cell. You can use the SUMIFS function to do so:

=SUMIFS(E2:E100,B2:B100,"Sell",F2:F100,">0")

Whenever you change the filter in your Sheet this cell will update its value without having to write the explicit condition in the SUMIFS formula.

---

Note: You can hide the Subtotal-Helper column so that your visual output won't be affected.

Result

References

SUBTOTAL

SUMIFS

Answer 2

Try below formula-

=SUM(FILTER($E$2:$E$100,($C$2:$C$100="Box")*($B$2:$B$100=F6)))