function or formela

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
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
 

Some videos you may like

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"

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152

ADVERTISEMENT

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
Joined
Oct 17, 2005
Messages
152
I found the solution


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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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... :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,483
Members
412,668
Latest member
Lukep22
Top