Please help me to understand this 'SUMPRODUCT' formula.

[maanalyst]

Hi,

I want to understand this formula
=SUMPRODUCT((AB3<AB$3:AB$31)/COUNTIF(AB$3:AB$31,AB$3:AB$31))+1

Can someone please explain how this formula is working?

 

[JoeMo]

Hi,

I want to understand this formula
=SUMPRODUCT((AB3<AB$3:AB$31)/COUNTIF(AB$3:AB$31,AB$3:AB$31))+1

Can someone please explain how this formula is working?

The formula returns the number of values in the range AB3:AB31 that exceed the value of AB3 and then adds 1 to that count. To see how the formula is evaluated go to the ribbon Formulas>Formula Auditing>Evaluate Formula.
I think you can simplify the formula to use only a single worksheet function, like this:
=SUMPRODUCT(--(AB3:AB31>AB3))+1

 

[6StringJazzer]

The formula returns the number of values in the range AB3:AB31 that exceed the value of AB3 and then adds 1 to that count.

Wouldn't it be a bit more straightforward to do this:

=COUNTIF(AB$3:AB$31, ">"& AB3) + 1

 

[bebo021999]

The formula returns the number of values in the range AB3:AB31 that exceed the value of AB3 and then adds 1 to that count. To see how the formula is evaluated go to the ribbon Formulas>Formula Auditing>Evaluate Formula.
I think you can simplify the formula to use only a single worksheet function, like this:
=SUMPRODUCT(--(AB3:AB31>AB3))+1

No, I dont think so
this formula to count unique distinct value that greater than AB3, then plus 1
In which:
1/COUNTIF(AB$3:AB$31,AB$3:AB$31) to divide value by how many times it appears: For instant: value 3 appears to 2 times, become 1/2 & 1/2
(AB$3:AB$31>AB3)/COUNTIF(AB$3:AB$31,AB$3:AB$31) to narrow results with condition >AB3
Then
SUMPRODUCT(...) to count
See my below sheet:

Book1
	AB	AC	AD	AE
2	Value list	1/countif	> AB3 condition	results range
3	2	1/3	FALSE	0
4	1	1	FALSE	0
5	2	1/3	FALSE	0
6	2	1/3	FALSE	0
7	3	1/2	TRUE	1/2
8	3	1/2	TRUE	1/2
9	4	1/23	TRUE	1/23
10	4	1/23	TRUE	1/23
11	4	1/23	TRUE	1/23
12	4	1/23	TRUE	1/23
13	4	1/23	TRUE	1/23
14	4	1/23	TRUE	1/23
15	4	1/23	TRUE	1/23
16	4	1/23	TRUE	1/23
17	4	1/23	TRUE	1/23
18	4	1/23	TRUE	1/23
19	4	1/23	TRUE	1/23
20	4	1/23	TRUE	1/23
21	4	1/23	TRUE	1/23
22	4	1/23	TRUE	1/23
23	4	1/23	TRUE	1/23
24	4	1/23	TRUE	1/23
25	4	1/23	TRUE	1/23
26	4	1/23	TRUE	1/23
27	4	1/23	TRUE	1/23
28	4	1/23	TRUE	1/23
29	4	1/23	TRUE	1/23
30	4	1/23	TRUE	1/23
31	4	1/23	TRUE	1/23
32
33		4		2
34		count unique distinct value		count unique distinct value that >AB3
35
36			plus 1	3
Sheet1

Cell Formulas
Range	Formula
AC3:AC31	AC3	=1/COUNTIF($AB$3:$AB$31,AB3)
AD3:AD31	AD3	=AB3>$AB$3
AE3:AE31	AE3	=AC3*AD3
AC33,AE33	AC33	=SUM(AC3:AC31)
AE36	AE36	=SUMPRODUCT((AB$3:AB$31>AB3)/COUNTIF(AB$3:AB$31,AB$3:AB$31))+1

 

[maanalyst]

The formula returns the number of values in the range AB3:AB31 that exceed the value of AB3 and then adds 1 to that count. To see how the formula is evaluated go to the ribbon Formulas>Formula Auditing>Evaluate Formula.
I think you can simplify the formula to use only a single worksheet function, like this:
=SUMPRODUCT(--(AB3:AB31>AB3))+1

Thank you. It's helpful.

 

[JoeMo]

Thank you. It's helpful.

You are welcome but please note bebo021999's post #4 which explains why the simplification I suggested fails to count unique distinct values greater than AB3.