Sum of data across multiple columns & rows based on Criteria

To sum data across multiple rows and columns based on a specific criteria, a standard SUMIFS will usually fail or return an error because it expects a single-column or single-row sum range.

The most efficient and reliable way to handle this without changing your data structure is by using the SUMPRODUCT function. It evaluates arrays natively and allows multi-dimensional math.

You can try this formula format:

=SUMPRODUCT((A2:A10="YourCriteria")*(B2:D10))

How this works:

- (A2:A10="YourCriteria") checks your criteria column and creates an array of TRUE (1) and FALSE (0) values.

- *(B2:D10) multiplies that column array against the entire multi-column numeric data matrix.

- Finally, SUMPRODUCT adds up all the calculated products, giving you the exact sum across all rows and columns.

Alternatively, if you are using modern Office 365, you can also wrap a SUM around a FILTER function like this:

=SUM(FILTER(B2:D10, A2:A10="YourCriteria", 0))

Give this a try, and it should resolve the issue you are facing with your dataset!