Скачать или смотреть How to Use COUNTIFS Across Multiple Columns with Multiple Criteria in Excel and LibreOffice Calc

Learn how to count occurrences of a specific value across multiple columns with additional conditions using SUMPRODUCT instead of COUNTIFS, applicable in both Excel and LibreOffice Calc.
---
This video is based on the question https://stackoverflow.com/q/79363726/ asked by the user 'lonix' ( https://stackoverflow.com/u/9971404/ ) and on the answer https://stackoverflow.com/a/79363756/ provided by the user 'utsavsheta' ( https://stackoverflow.com/u/19523891/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: COUNTIFS across multiple columns with multiple criteria

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
The Challenge: Counting with Multiple Criteria Across Columns

When working with spreadsheets, it’s common to count occurrences of a certain value across multiple columns while enforcing additional conditions on other columns. For example, imagine a dataset with tags across three columns and you want to count how many times "foo" appears in any of those columns—but only where other columns meet specific criteria.

Why COUNTIFS Falls Short

The COUNTIFS function works great when conditions apply to single columns. However, it does not support checking multiple columns simultaneously like COUNTIFS($A:$C, "foo", ...). Attempting this in LibreOffice Calc results in errors (Err:502), and in Excel it doesn’t return the correct results either.

Common Workaround and Its Limitations

You can combine multiple COUNTIFS statements, one per column, then add them together:

[[See Video to Reveal this Text or Code Snippet]]

While this works, it becomes unwieldy with many columns or more complex conditions, making your formulas hard to read and maintain.

Elegant Solution: Use SUMPRODUCT

Instead of trying to force COUNTIFS to handle multiple columns at once, use the SUMPRODUCT function to perform logical multiplication and sum the results in one go.

How it Works

SUMPRODUCT can evaluate multiple criteria arrays simultaneously and sum all TRUE results where conditions match.

Example Formula

Assuming your data range is from row 2 to 9:

[[See Video to Reveal this Text or Code Snippet]]

(A2:C9="foo") creates a matrix of TRUE/FALSE indicating where "foo" appears in any of the three columns.

(D2:D9=1) and (E2:E9=2) apply the additional conditions on columns D and E.

Multiplying these logical arrays coerces TRUE/FALSE to 1/0, filtering only rows and columns fulfilling all conditions.

The result is a count of all appearances of "foo" in columns A through C where other criteria are met.

Benefits

Single, maintainable formula

Works both in Excel and LibreOffice Calc

Easily extendable by adjusting ranges or criteria

Summary

When counting items matching multiple criteria across several columns in Excel or LibreOffice Calc:

Avoid trying to use COUNTIFS on multi-column ranges.

Use a sum of multiple COUNTIFS if the range is small, but expect cumbersome formulas.

Use SUMPRODUCT for a concise, scalable, and efficient solution.

This approach keeps your formulas neat, powerful, and easy to manage.