SOLVED

# Countif Function

Copper Contributor

# Countif Function

Looking to count the number of values in a column without including duplicates (Column N contains values but has a ton of duplicates, and I cannot remove them from the worksheet)

The function must include:

- a date range that the values must be within (i.e. column E contains the dates)

- Must contain a specific word (i.e. call the word XXX, and the word can be located in column T)

Is this reasonably achievable in a single formula?

5 Replies
best response confirmed by zbramwell (Copper Contributor)
Solution

# Re: Countif Function

Let's say the start date is in Z1 and the end date in Z2.

=COUNTA(UNIQUE(FILTER(N2:N10000, (E2:E10000>=Z1)*(E2:E10000<=Z2)*(T2:T10000="XXX"))))

# Re: Countif Function

to do this without the date ranges, could I remove them and leave the rest of the formula as is?

# Re: Countif Function

Yes:

=COUNTA(UNIQUE(FILTER(N2:N10000, T2:T10000="XXX")))

# Re: Countif Function

Assuming that unique values of 0 were being counted, causing the total number of unique items to be overstated due to line items containing 1 instead of 0, how could I adjust for this?

i.e, if c4=0, return 0 for the entire formula.

# Re: Countif Function

Sorry, I don't understand. Can you try to explain it in more detail?

1 best response

Accepted Solutions
best response confirmed by zbramwell (Copper Contributor)
Solution

# Re: Countif Function

Let's say the start date is in Z1 and the end date in Z2.

=COUNTA(UNIQUE(FILTER(N2:N10000, (E2:E10000>=Z1)*(E2:E10000<=Z2)*(T2:T10000="XXX"))))