# can someone help my formula

#### sitelbanat

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

### 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).

#### Andrew Poulsom

##### MrExcel MVP
Please give a detailed explanation of how you arrive at 4.4.

#### texasalynn

##### Well-known Member
=COUNTIF(O69:O82,">0")+(SUMIF(O69:O82,">0",E69:E76))

but I think the answer should 5.4

#### sitelbanat

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

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

#### sitelbanat

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

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)

#### sitelbanat

##### Board Regular
Yes texasalynn. your right am sorry the count of O=4 so the answer would 3.4

#### Andrew Poulsom

##### MrExcel MVP
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
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.

Replies
24
Views
432
Replies
1
Views
73
Replies
3
Views
363
Replies
3
Views
87
Replies
4
Views
76