# 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
```=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)) ```

``` var hupso_services_t=new Array("Twitter","Facebook","Google Plus","Pinterest","Linkedin","Reddit");var hupso_background_t="#EAF4FF";var hupso_border_t="#66CCFF";var hupso_toolbar_size_t="medium";var hupso_image_folder_url = "";var hupso_url_t="";var hupso_title_t="Tom%27s%20Tutorials%20For%20Excel%3A%20Summing%20Cells%20in%20Even%20or%20Odd%20Numbered%20Rows%20Only"; ```
``` ‹ Tom’s Tutorials For Excel: Verifying a Time as AM or PM Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times › Posted in Tom's Tutorials for Excel Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis, Worksheets Workbooks ```
``` 13 comments on “Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only” drewbbc says: May 3, 2015 at 9:29 am 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 Reply Tom Urtis says: May 3, 2015 at 9:42 am 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. Reply Tom Urtis says: May 3, 2015 at 9:45 am 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. Reply Naresh says: September 24, 2017 at 12:03 am Dear sir , kindly explain the how to make subtotal for sumproduct . Reply Tom Urtis says: September 24, 2017 at 9:07 am 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. Reply Naresh says: September 26, 2017 at 10:48 pm 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 Reply Tom Urtis says: October 2, 2017 at 6:37 pm 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) Reply Microsoft Servers says: September 12, 2019 at 7:35 pm I am so happy I found this blog. It would of been a life saver a few months ago. Thanks https://megacomputertech.com/ Reply Tom Urtis says: September 12, 2019 at 8:22 pm Thank you. Reply Rakib says: August 11, 2020 at 9:45 am This is so very much helpful for me. Can you please explain how does this formula work. I mean this function mathematical explanation. Reply Tom Urtis says: August 11, 2020 at 5:20 pm 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. Reply Yonna says: February 20, 2024 at 10:59 pm It works. What about the average in even or odd numbered rows? Reply Tom Urtis says: February 21, 2024 at 9:00 am 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. Reply Leave a Reply Your email address will not be published. Required fields are marked *Comment * Name * E-mail * Δdocument.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); ```
``` CATEGORIESActiveX / Form Controls (10)Array Formulas (27)Cells Ranges Lists (122)Charts and Chart Sheets (1)Comments / Screen Tips (9)Conditional Formatting (15)Cut Copy Paste Clear Fill (16)Data validation (12)Date Time (53)Duplicates Uniques (13)Filter Subtotal Group (9)Find Replace Substitute (18)Formats Fonts Styles Colors (49)Formulas Functions (152)Keyboard Mouse (35)Links Hyperlinks Web Email (12)Macros Events Classes API (110)Numbers and Decimals (49)Pictures Shapes Graphics (4)Pivot Tables / Pivot Charts (10)Printing Page Setup (7)Sort (7)Text and Constants (19)Tips and Tricks (55)User-Defined Functions (6)UserForms (10)Word Outlook Access PPT (8)Worksheets Workbooks (60) Tweets by @TomUrtis !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); ```
``` ```
``` (function () { var c = document.body.className; c = c.replace(/woocommerce-no-js/, 'woocommerce-js'); document.body.className = c; })(); /* <![CDATA[ */ var BG_SHCE_USE_EFFECTS = "0"; var BG_SHCE_TOGGLE_SPEED = "400"; var BG_SHCE_TOGGLE_OPTIONS = "none"; var BG_SHCE_TOGGLE_EFFECT = "blind"; /* ]]> */ /* <![CDATA[ */ var wc_order_attribution = {"params":{"lifetime":1.0000000000000000818030539140313095458623138256371021270751953125e-5,"session":30,"ajaxurl":"https:\/\/www.atlaspm.com\/wp-admin\/admin-ajax.php","prefix":"wc_order_attribution_","allowTracking":true},"fields":{"source_type":"current.typ","referrer":"current_add.rf","utm_campaign":"current.cmp","utm_source":"current.src","utm_medium":"current.mdm","utm_content":"current.cnt","utm_id":"current.id","utm_term":"current.trm","session_entry":"current_add.ep","session_start_time":"current_add.fd","session_pages":"session.pgs","session_count":"udata.vst","user_agent":"udata.uag"}}; /* ]]> */ (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) })(window,document,'script','https://www.google-analytics.com/analytics.js','ga'); ga('create', 'UA-96540138-1', 'auto'); ga('send', 'pageview'); San Francisco, California1-925-310-4170tom@atlaspm.comdocument.body.style.setProperty( "padding-bottom", document.getElementById('scb-wrapper').offsetHeight+'px', "important" ); ```