can someone help my formula

[sitelbanat]

=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

 

[Andrew Poulsom]

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

 

[texasalynn]

=COUNTIF(O69:O82,">0")+(SUMIF(O69:O82,">0",E69:E76))

but I think the answer should 5.4

 

[sitelbanat]

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]

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

 

[sitelbanat]

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]

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]

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

 

[Andrew Poulsom]

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]

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.