Distinct collect formula error

Mel C

Hi

I am trying to calculate the value of live accounts from a total sales pipeline

The total sales pipeline lets say is $10,569,815 the customers live value is only $292,200,00

but when I use the following formula : =SUM(DISTINCT(COLLECT(ARR14:ARR224, [Sales Stage]14:[Sales Stage]224, "live"))) it only calculates a total of $280,200.00 so is short $12,000.00 but all the cells are captured in both data range sets?

Data range 1 is the ARR totals and Data range 2 is the Sales Stage that indicates if it is live or not

Any ideas as to what other formula that I can use that will calculate this correctly.

Many thanks

Leibel S

Why do you need the DISTINCT?

You probably have 1 or more values in the Sales stage range that are the same, and your DISTINCT function is filtering them out