# function or formela

#### sitelbanat

##### Board Regular
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.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### fairwinds

##### MrExcel MVP
Hi,

+(sum(E69:E82)<0)

at the end of your formula.

Thank You

#### sitelbanat

##### Board Regular

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

##### Board Regular
I found the solution

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

I solved it

#### fairwinds

##### MrExcel MVP
sitelbanat said:
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

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

Replies
27
Views
925
Replies
7
Views
192
Replies
3
Views
104
Replies
3
Views
132
Replies
1
Views
67