CODEWITHSUNDEEP
excelexcel-formula
BOB
BOB

Reputation: 700

Excel SUM, FREQUENCY, IF, MATCH

Why the first formula work and the second formula doesn't, the difference is AND condition. 

=SUM(--(FREQUENCY(IF(J2:J1436>L1,MATCH(A$2:A$1436,A$2:A$1436,0)),ROW(A$2:A$1436)-ROW(A$2)+1)>0))`

=SUM(--(FREQUENCY(IF(AND(J$2:J$1436>L$1,I$2:I$1436<L$1),MATCH(A$2:A$1436,A$2:A$1436,0)),ROW(A$2:A$1436)-ROW(A$2)+1)>0))`

Upvotes: 1

Views: 702

Answers (1)

Mrig
Mrig

Reputation: 11712

Try this:

=SUM(--(FREQUENCY(IF((J$2:J$1436>L$1)*(I$2:I$1436<L$1),MATCH(A$2:A$1436,A$2:A$1436,0)),ROW(A$2:A$1436)-ROW(A$2)+1)>0))

Use * instead of AND.

Upvotes: 1

Related Questions