Find how many days have positive values from a list?

[Mr.Castle]

From S4:S72 i have a list of numbers that are related to a list of dates C4:C72 ,some dates are repeated. I need to find how many days have positive values?

[dosydos]

=countif($S$4:$S$72,">0")

[Mr.Castle]

The formula is not relating the dates C4:C72.

Example:

C4=10/11/2018 S4= -200
C5=11/11/2018 S5= 500 11/11/2018 IS -100 than a negative day
C6=11/11/2018 S6= -600
C7=12/11/2018 S7= -50 12/11/2018 is 50 positive
C8=12/11/2018 S8= 100
C9=13/11/2018 S9= 100 13/11/2018 is 100 positive
C10=14/11/2018 S10= -330
C11=15/11/2018 S11= 200 15/11/2018 is 400 positive
C12=15/11/2018 S12= 200

There are 4 positive days. The formula must calculate the number of positive numbers S4:S12 related to dates C4:C12.

[José Augusto]

Try this

=SUMPRODUCT(--(
INDEX(($C$4:$C$12)*($S$4:$S$12>0),,)*FREQUENCY(
INDEX(($C$4:$C$12)*($S$4:$S$12>0),,),
INDEX(($C$4:$C$11)*($S$4:$S$11>0),,))>0))

Note: last reference in this formula is $S$11 (one less, not a mistake)

[Mr.Castle]

Hi , the formula is counting 0 as positive in S4:S12.

Example:

C9=11/10/2018 S9=-4,0
C10=11/10/2018 S10=0

And is counted as positive

[Mr.Castle]

I notice other problem.

Example:

C9 = 10/10/2018 S9=44
C10=10/10/2018 S10=-90

Is counting as a positive value when a greater negative number comes in second, but if was the inverse s9=-90 and s10=44 it would count correctly as negative. So the formula must add and subtract from any related S number from the same dates C, and find wether the result is positive, like the example above. Finally it must count all positive values.

[José Augusto]

Hi MrCastle

I do not understand why the formula gives you 0 (for me is 4). The formula gives you the number of days that has at least a positive value

The new formula gives you the number of days with a positive aggregate sum (3) and use a helper column T

Please Login or Register  to view this content.

Where column T has the following formula:

Please Login or Register  to view this content.

See the file for clarification

[Mr.Castle]

The new formula worked ,

Thank you José Augusto