Repeating 'SUMIF' formula for entire column

[Guest]

My sumif formula: sum(if(f2+f3>0,"1"))+(if(f3+f4>0,"1")) Works fine to add the values in each set of two rows and if greater than 0 returns a-1, which is what I need. Then I will sum the result from F2 to F50 or whatever. My problem is two fold. (1) Is there a way to specify condition to repeat this formula for every set of 2 rows for the entire column, currently at 250rows without manually entering each 2-cell formula? (2) While manually entering this formula for rows F2 though F50 worked, then my formula gets too long and goes over the character limit. What other options do I have in naming this range to compare every two rows and then sum all results. I hope this makes some sense to you experienced folks out there.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--((F2:F249)+(F3:F250)>0))

 

[Guest]

I appreciate the help on this. However I did try the formula on just 4 cells and the value returned is 2, but should be 1 (see example 1 below.
I need to sum the results from each series of 2 rows (F2+F3, F4+F5, etc which will return a 0 or 1 ) and then sum the column results.
Example 1
F2 AM=
F3 PM=0 this will return a value of zero with =sum(if(F2+F3>0,"1")
F4 AM=
F5 PM=1 this will return a value of 1 with =sum(if(F4+F5>0,"1"))
This will give a total sum of

Example 2:
F6 AM=1
F7 PM=0 this will return a value of 1 with =sum(ifF2+F3>0,"1")
F8 AM=
F9 PM=1 this will return a value of 1 with =sum(if(F3+F4>0,"1")
This will return a value of 2

 

[Peo Sjoblom]

Insert a help column, leave G2 blank, in G3 put

=IF(F2+F3>0,1,0)

select both G2 and G3, drag the fillhandle down
to G250
use

=SUM(G2:G250)



--

Regards,

Peo Sjoblom

I appreciate the help on this. However I did try the formula on just 4

cells and the value returned is 2, but should be 1 (see example 1 below.)

I need to sum the results from each series of 2 rows (F2+F3, F4+F5, etc

which will return a 0 or 1 ) and then sum the column results.

 

[Guest]

Mark, Thanks. I did try the original formula you provided, but it did not seem to compute what I wanted it to compute. I have worked it out for now (I think) by using another set of colums as 'help columns' like Peo suggested that contain the formula to calculate the value for each set of 2 rows and then sum those results. Appreciate your help.