Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only
Here are formulas using the SUMPRODUCT function that sum a range of cells that reside in only the even-numbered or odd-numbered rows.
Summing only the even-numbered rows.
The formula in selected cell C21 copied to cell F21 is
=SUMPRODUCT(C4:C18,MOD(ROW(C4:C18)+1,2))
Summing only the odd-numbered rows.
The formula in selected cell C22 copied to cell F22 is
=SUMPRODUCT(C5:C19,MOD(ROW(C5:C19)+0,2))
Tom — uplifting into Powerquery — then depivoting then dollars – then uplifting to the data model with some dax (IMV) delivers a far more flexible result that using a simple sumproduct
You’d apply all that to this case? Not for me. All those analytical tools have their place but in this example that’d be a lot of overcomplicated activity to just to get a simple sum.
Not sure I understand. Can you not just set the formula for whatever the beginning row is, as this example does for row 2, and copy down? Sorry, I must be missing the point of your question.
Dear sir ,
kindly explain the how to make subtotal for sumproduct .
I’m happy to help you if I can, but your comment can entail numerous possibilities. Please offer an example with details of what you are working with (ranges involved, data examples), what you want to to do, and your expected results.
Thanks for your reply – the below is my requirement
Name Item Plan / Actual Commitment
Textile Cover Plan 350
Textile Cover Actual 100
Textile Plate Plan 350
Textile Plate Actual 100
Engineering Sheet Plan 350
Engineering Sheet Actual 100
Engineering Rod Plan 500
Engineering Rod Actual 100
Commercial Angle Plan 600
Commercial Angle Actual 100
Commercial Pipe Plan 700
Commercial Pipe Actual 100
Commercial Lever Plan 350
Commercial Lever Actual 100
Total Plan
Total Actual
IF I apply the filter in name or item I want the total plan and total actual. for the filter data onlhy
Assuming your data goes from A2:C15, then these headers in A16:A23 and these corresponding formulas in B16:B23 worked for me.
Plan only =SUMPRODUCT((RIGHT(B2:B15,4)=”Plan”)*C2:C15)
Actual only =SUMPRODUCT((RIGHT(B2:B15,6)=”Actual”)*C2:C15)
Textile Plan =SUMPRODUCT(((A2:A15)=”Textile”)*(RIGHT(B2:B15,4)=”Plan”)*C2:C15)
Engineering Plan =SUMPRODUCT(((A2:A15)=”Engineering”)*(RIGHT(B2:B15,4)=”Plan”)*C2:C15)
Commercial Plan =SUMPRODUCT(((A2:A15)=”Commercial”)*(RIGHT(B2:B15,4)=”Plan”)*C2:C15)
Textile Actual =SUMPRODUCT(((A2:A15)=”Textile”)*(RIGHT(B2:B15,6)=”Actual”)*C2:C15)
Engineering Actual =SUMPRODUCT(((A2:A15)=”Engineering”)*(RIGHT(B2:B15,6)=”Actual”)*C2:C15)
Commercial Actual =SUMPRODUCT(((A2:A15)=”Commercial”)*(RIGHT(B2:B15,6)=”Actual”)*C2:C15)
I am so happy I found this blog. It would of been a life saver a few months ago.
Thanks https://megacomputertech.com/
Thank you.
This is so very much helpful for me. Can you please explain how does this formula work. I mean this function mathematical explanation.
Here is how the formula works:
First, the range of interest is C4:C18, and the issue is to sum just the even numbered rows, or the odd numbered rows for C5:C19. Just looking at the even numbered rows formula, The SUMPRODUCT formula compares two arrays that are ranges. One range is the full range of C4:C18. The other range is an evaluation for which cells in the range satisfy the requirement of being in the even numbered rows. The MOD function is using the number 2 as the divisor for every other row. If you were to select the MOD(ROW(C4:C18)+1,2) portion of the formula in the formula bar and hit the F9 key, you would see this: {1;0;1;0;1;0;1;0;1;0;1;0;1;0;1}. Now all the SUMPRODUCT function does is to look at only the TRUE cells (which are identified by the 1’s) and sum what is in those cells as they fall into the first array’s range of C4:C18. For the odd numbered rows it is basically the same process but using the 0 instead of 1 number argument to look at the odd rows.