Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only

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

Summing only the odd-numbered rows.
The formula in selected cell C22 copied to cell F22 is

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
13 comments on “Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only
  1. drewbbc says:

    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

    • Tom Urtis says:

      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.

    • Tom Urtis says:

      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.

  2. Naresh says:

    Dear sir ,

    kindly explain the how to make subtotal for sumproduct .

    • Tom Urtis says:

      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.

      • Naresh says:

        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

        • Tom Urtis says:

          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)

  3. Microsoft Servers says:

    I am so happy I found this blog. It would of been a life saver a few months ago.

  4. Rakib says:

    This is so very much helpful for me. Can you please explain how does this formula work. I mean this function mathematical explanation.

    • Tom Urtis says:

      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.

  5. Yonna says:

    It works.
    What about the average in even or odd numbered rows?

    • Tom Urtis says:

      Using my pictured example, in cell B23 is this formula:

      In cell B24 is this formula:

      If you are not on 365 (for example if you are using version 16 or earlier) then confirm the formula by pressing Ctrl+Shift+Enter, not just with Enter.

Leave a Reply

Your email address will not be published. Required fields are marked *


  • Facebook
  • Twitter
  • Instagram
  • Linkedin
  • Youtube