CODEWITHSUNDEEP
excelexcel-formula
Alien_Explorer
Alien_Explorer

Reputation: 867

Excel: How to count every n cell, whether the value is above or below 0

I have used {=STDEV.P(IF(MOD(ROW($E$1:$E$1000),4)=1,IF($E$1:$E$1000<>"",$E$1:$E$1000)))} to calculate sigma of every 4th cell in column E:E aggregated, same goes for the mean and sum. Now, I find myself with a problem as {=countif(IF(MOD(ROW($E$1:$E$1000),4)=1,IF($E$1:$E$1000<>"",$E$1:$E$1000)),">0")} does not work. Can you please advise how to reformat the formula so it can count number of positive values every x (4th) number of cells?

Thx

Upvotes: 1

Views: 217

Answers (1)

Lorientas
Lorientas

Reputation: 66

I believe that sum(if(...)) will work better with array formulas. I experienced similar issues in array formulas with different functions. Also since sum function disregards "" you don need to use $E$1:$E$1000<>"" condition. I believe that following array formula will do the trick for your case. {=SUM(IF((MOD(ROW($E$1:$E$1000),4)=1)*($E$1:$E$1000>0),1,0))} .

Upvotes: 1

Related Questions