Hi everyone,
I have a very large data set where I need to sum the rows but exclude some so for example sum 1:100 but miss out every 20th row. This should give me the sum from 1 to 100 and miss out rows 20,40,60,80 and 100.
Any help would be greatly appreciated.
Thanks
Reem
Is there any other factor that could be used to 'flag' which cells should NOT be summed?
Like if column A it has text "Subtotal" then don't include that row ?
This arrayed formula will work
=SUM(A1:A100*(MOD(ROW(A1:A100),20)>0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Does that work for you?
or non-arrayed
=SUMPRODUCT((A1:A100), --(MOD(ROW(A1:A100),20)>0))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
=SUMPRODUCT(--(MOD(ROW(A1:A100),20)<>0),A1:A100) will do that
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Welcome reem,
Assuming Column A:A contains values, then in Cell B1 =IF(IF(MOD(ROW(A1),20),0,1)=0,A1,0)
and drag it down. Refer Sample attachment.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks