Скачать или смотреть How to Use SUMPRODUCT for Conditional Counting in Excel

Learn how to effectively use the `SUMPRODUCT` function in Excel to count values based on multiple criteria. This guide breaks down your formula challenges step by step.
---
This video is based on the question https://stackoverflow.com/q/62759223/ asked by the user 'Cameron Claydon' ( https://stackoverflow.com/u/12486812/ ) and on the answer https://stackoverflow.com/a/62759738/ provided by the user 'Scott Craner' ( https://stackoverflow.com/u/4851590/ ) 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 with multiple criteria but only needing one met

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 write me at vlogize [AT] gmail [DOT] com.
---
Mastering Conditional Counting in Excel with SUMPRODUCT

When working with large datasets in Excel, you may find yourself needing to count entries based on multiple conditions. One common challenge is counting values in a range where one criterion must be met while ensuring other related fields contain values as well.

In this guide, we’ll address a specific scenario: counting initials in a range only if related ranges have values. We’ll explore how to correct your formula using the SUMPRODUCT function, making your data analysis more efficient.

The Problem

Suppose you have a range of initials in cells E6 to E53, and you want to count how many times a specific initial appears that is also specified in cell D60. However, you want to add two additional criteria:

The cells in the range P6 to P53 must not be blank.

The cells in the range S6 to S53 must also not be blank.

The original formula attempted was:

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

Unfortunately, this counts the initials regardless of whether the cells in the other two criteria are filled.

The Solution

To solve this problem, we replace the COUNTIFS function with SUMPRODUCT, which provides a more flexible method for counting based on multiple conditions.

Using SUMPRODUCT for AND Criteria

If you need to count initials only when both P6:P53 and S6:S53 are not blank, you can use the following formula:

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

Breakdown of the Formula:

(E6:E53=D60): This checks if each entry in the range E6 to E53 matches the initial specified in cell D60.

(LEN(P6:P53) 0): This verifies that the cells in range P6 to P53 are not blank (i.e., their length is greater than 0).

(LEN(S6:S53) 0): Similarly, this checks for the S6 to S53 range.

The * signifies logical AND, meaning all conditions must be true for the counts to add up.

Using SUMPRODUCT for OR Criteria

Alternatively, if you want to count initials when either P6:P53 or S6:S53 contains values, you can modify the formula like this:

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

Breakdown of the OR Formula:

The first portion remains the same, checking if initials match.

((LEN(P6:P53) 0)+ (LEN(S6:S53) 0) 0): This structure uses addition instead of multiplication, allowing for an OR condition. If either length is greater than 0, the condition is true.

Conclusion

Using the SUMPRODUCT function provides a powerful alternative for counting based on multiple criteria in Excel. Whether you're counting when both conditions need to be met (AND) or one condition suffices (OR), this formula can help streamline your data analysis process.

Feel free to apply this knowledge to your datasets and simplify your counting tasks in Excel!