**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.

It works.

What about the average in even or odd numbered rows?

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

=AVERAGE(IF(MOD(ROW(B6:B21),2)=0,B6:B21))

In cell B24 is this formula:

=AVERAGE(IF(MOD(ROW(B6:B21),2)=1,B6:B21))

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.