can someone help my formula

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
=COUNTIF(O69:O82,">0", IF(O69:O82,">0",(E69:E82 *1),0),0)


IF O range >0 then Multiply that cell count by the same row in E range.
for example if

O range

O69=2356
O72=5625
O74=5625
O76=5642

E range

E69=.80
E72=.60
E74=0
E76=0

the answer should be 4.4 for O85 that's where the formula belongs
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
=COUNTIF(O69:O82,">0")+(SUMIF(O69:O82,">0",E69:E76))

but I think the answer should 5.4
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
O range count =5

so since O69 and O72 have a value then you look at E range E69=.80 and E72=.60 which =1.4 so since 2 cells have values, you subtract 2 from the 5 count and add 1.4 to the count which makes it 4.4
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

ok so I only see 4 counts in O
are we missing something?
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
I think the formula needs to do this to be able to get the results am looking for

I need a formula that would count how many value in O69:O82 range >0 then out of those cells count how many values are >0 in E69:E82. Then take the total count of E69:E82 range and subtract that count from O69:O82 range total then Sum the values in E69:E82 Range and add to O69:O82 range.
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152

ADVERTISEMENT

Maybe something like this, but this formula is not working

=COUNTIF(O69:O82,">0"),COUNT(O69:O82,">0", COUNT(E69:E82)-COUNT(O69:O82)+sum(E69:E82),0)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

=SUMPRODUCT(--(O69:O82>0),--(E69:E82=0))+SUMIF(E69:E82,">0")

which will count the cells in O69:O82 that are greater than zero if the corresponding cell in column E is zero, and add the sum of the greater than zero cells in E69:E82.

Given the data you posted it returns 3.4.
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
No it return 5.2 because you would have to count how many value in E range then subtract that number from the total count in O. then Add the sum of E range to O range count.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,695
Members
412,481
Latest member
nhantam
Top