every other on a nested sumif

[andrewb90]

Hello All,


I know there are ways to count every nth value however, I am struggling to apply it to my current formula. I am trying to do every other cell.(in the 165:178 portions))

=(SUM(IF(E$165:E$178<$B89,IF(F$165:F$178>$A89,IF(F$165:F$178<$B89,F$165:F$178,$B89)-IF(E$165:E$178>$A89,E$165:E$178,$A89))))*24)

Any guidance would be appreciated!

Thanks,

Andrew

 

[Eric W]

First of all, I found your formula a bit tough to decipher, so I rewrote it like so:

=SUMPRODUCT(--(E$165:$E178<$B89),--(F$165:F$178>$A89),IF(F$165:F$178<$B89,F$165:F$178,$B89)-IF(E$165:E$178>$A89,E$165:E$178,$A89))*24

confirmed with Control-Shift-Enter. You should check to see that it does what you want, since I really can't create sample data to test it.

Once you have that, you can try:

=SUMPRODUCT(--(E$165:$E178<$B89),--(F$165:F$178>$A89),MOD(ROW($E$165:$E$178),2)=0,IF(F$165:F$178<$B89,F$165:F$178,$B89)-IF(E$165:E$178>$A89,E$165:E$178,$A89))*24

which will only sum up the even rows. Change the red zero to a 1 to sum the odd rows.

 

[andrewb90]

The first formula works (had to fix the 2nd $ though in order to fill the formula down). However the second formula calculates neither even nor odd rows, it just returns a value of zero

 

[Eric W]

Darn, I made a tiny tweak between my test sheet and posting it, and I introduced a glitch. Try it this way:

=SUMPRODUCT(--(E$165:$E178<$B89),--(F$165:F$178>$A89),--(MOD(ROW($E$165:$E$178),2)=0),IF(F$165:F$178<$B89,F$165:F$178,$B89)-IF(E$165:E$178>$A89,E$165:E$178,$A89))*24

 

[andrewb90]

[COLOR=#333333]=SUMPRODUCT(--(E$165:E[/COLOR][COLOR=#ff0000]$[/COLOR][COLOR=#333333]178<$B89),--(F$165:F$178>$A89)[/COLOR],--(M[COLOR=#333333]OD(ROW($E$165:$E$178),2[/COLOR])=0),[COLOR=#333333]IF(F$165:F$178<$B89,F$165:F$178,$B89)-IF(E$165:E$178>$A89,E$165:E$178,$A89))*24[/COLOR]

Works perfect like this! Thank you!

 

[Eric W]

You're welcome!