I need some help with a formula to SUM more than 30 numbers. Specifically, I need to SUM even-numbered rows, (G2:I2, G4:I4, G6:I6, . . .all the way to G62:I62) Since this formula has 31 numbers, Excel gives me an error and says that it can only SUM 30 numbers. Is there another formula that I can use to help me calculate this? Thanks in advance.
SUM More than 30??
-
-
-
This is probably convoluted but will work.
Add a column, and in that insert numbers (for row 1, 2, etc.) then copy/fill down to the 62nd row.
Then use SUMIF based on whether that new column has an even number.
-
Thanks :spin: it works great!
-
if you need this "automated", you could try something along these lines :
{=SUM(IF(MOD(ROW(G2:G62),2)=0,G2:I62))}
the curley brackets around the formula are inserted by Excel automatically after you enter the formula as follows :
type (or paste) in the formula : =SUM(IF(MOD(ROW(G2:G62),2)=0,G2:I62)) and hit CNTRL and SHIFT and ENTER instead of the normal ENTER after you enter a formula
looking at the formula itself :
this is saying "SUM the range of G2:I62, IF blah blah blah =0"
the blah blah blah is as follows :
MOD(ROW(G2:G62),2)
this asks Excel to look at range G2:G62 and bring back the row number of each cell (in our case, it will be 2, then 3 then 4 etc etc through to 62).
The MOD function then looks at these answers and divides them all by "2" (in our case, it will be 0 then 1 then 0 then 1 etc etc throught to the 60th value of 0)
it's these 0 values that are passed to the above SUM function, effectively only summing on even rows
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!