function or formela

[sitelbanat]

This works great

=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82))


But I need to add a IF function at the end of the formela. I was wondering would I use AND IF or just IF?
I want the IF function to look at the selected cells from E69:E82 and if the total value is <0 than +1 to total count of O69:O82.


Please Help

 

[fairwinds]

Hi,

Just add

+(sum(E69:E82)<0)

at the end of your formula.

 

[sitelbanat]

Thank You

 

[sitelbanat]

but it dosent add +1

 

[sitelbanat]

why isnt this working?

=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82)),IF(SUM(E69:E82)<0,+1,"")

 

[sitelbanat]

I found the solution


=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82))+(SUM(E69:E82)<0+1)

 

[sitelbanat]

I solved it

 

[fairwinds]

I found the solution


=COUNTIF(O69:O82,">0")-(1-SUMPRODUCT(--(O69:O82>0),--(ISNUMBER(E69:E82)),E69:E82))+(SUM(E69:E82)<0+1)

That would add one if sum is less than one... but as long as you are happy...

 

[sitelbanat]

no its not working, I need it to coult the right amount if E range =0