Summing Unique Positive Values in Excel

Summing Unique Positive Values in Excel.This article presents a technique for summing only the unique positive values within an Excel dataset.

Issue: I’m currently dealing with an Excel file that contains a mix of text, negative values, positive values, and potential errors.

Summing Unique Positive Values in Excel

My objective is to calculate the sum of the distinct positive values within this dataset. The formula should disregard any text, negative numbers, or errors and provide the sum of the positive values.

Illustration: For instance, if the positive values are 1, 1, 5, 5, and 10, the desired outcome would be 16 (1 + 5 + 10).

The positive values have been conveniently marked with a green color. The data snapshot is as follows:

To achieve this in cell E2, the formula should be entered as follows: {=SUM(IF(ISNUMBER(A1:C13),IF(A1:C13>0,A1:C13/COUNTIF(A1:C13,A1:C13))))}

Note: This is an array formula, so remember to press CTRL + SHIFT + ENTER simultaneously.

The aforementioned formula effectively accumulates only the distinct positive numbers. This approach employs a combination of SUM, IF, ISNUMBER, and COUNTIF functions to attain the desired outcome.