# Count positive “1” value after the max negative instance appears.



## motilulla (Dec 30, 2022)

Using Excel 2010
Hello,

I am using following to get results + & - value In the Column “C” using the formula given by Peter under this link……








						Formula Sum Positives If not start sum Negative
					

Using Excel 2010  Hello,  I got results in the columns “G”, in the column “E” there is formula which drive result from Column “G”  In the column “C” I have set formula which sum if value in the column “G” is >””  But I am not able to set the formula if column =”” get negative values sum as shown...




					www.mrexcel.com
				




I would like to count how many times after the each max negative value like, for example after -1,-2-3 and so on positive value “1” has appeared. Here below is the example sheet in which I need a count formula in the column “F”

Note: my request if I could be possible, I can use the given formula in the excel 2000 also.

MrExcel Question.xlsxABCDEFG1234Formula Require 5Result- ValuesCount 1 After - Value6-1-1371-2582-319-1-4110-2-5111-3-6112-4-71131-8142-9153-1016-1-1117-2-12181-1319-1-1420-2-1521-3-1622-4-1723-524-625126-127-228129-130-231132233-134-235-336-437-538139-140141-142-243144245-146-247-348149250-151-252153254355-156157-158-259-360-461-562-663-764-865-966-1067-1168-1269-1370-1471-1572173-1747576Sheet3

Please help

Thank you all.

Regards,
Moti


----------



## Flashbond (Dec 30, 2022)

I think your last value (F12) is wrong. It is after -15, not -7. Paste the formula to F6:

```
=COUNTIFS($C$6:$C$73,1,$C$5:$C$72,E6)
```
There is a more fancy AGGREGATE function but I was not able make this work. Maybe someone will:

```
=AGGREGATE(3,6,ROW($C$6:$C$73)/($C$6:$C$73=1)/((($C$5:$C$72=E6)+($C$6:$C$73<>$C$5:$C$72))>0))
```


----------



## motilulla (Dec 30, 2022)

Flashbond said:


> I think your last value (F12) is wrong. It is after -15, not -7. Paste the formula to F6:


*Flashbond,* sorry yes it is my error it must go after -15,



Flashbond said:


> ```
> =COUNTIFS($C$6:$C$73,1,$C$5:$C$72,E6)
> ```


Your COUNTIFS formula works fine with excel 2010...but I need also formula which can work with excel version 2000 too is it possible?

Thank you so much for your help

Good Luck

Kind Regards,
Moti


----------



## Flashbond (Dec 30, 2022)

This will work for Excel 2000:

```
=SUMPRODUCT(--($C$6:$C$73=1),--($C$5:$C$72=E6))
```


----------



## motilulla (Dec 30, 2022)

Flashbond said:


> This will work for Excel 2000:
> 
> ```
> =SUMPRODUCT(--($C$6:$C$73=1),--($C$5:$C$72=E6))
> ```


*Flashbond**,* yes this formula worked like a charm with both versions 

I appreciate your help, I wish you a very happy and prosper New Year 2023 🍾

Good Luck

Kind Regards,
Moti


----------



## Flashbond (Dec 30, 2022)

motilulla said:


> *Flashbond**,* yes this formula worked like a charm with both versions
> 
> I appreciate your help, I wish you a very happy and prosper New Year 2023 🍾
> 
> ...


Thank you for your kind wishes  I wish the same for you


----------

