+ Reply to Thread
Results 1 to 4 of 4

How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

  1. #1
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    83

    How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

    Hello,

    How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

    sum.png

    I attached file with data.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

    Please try

    =SUMPRODUCT(A2:A9,D2:D9)

    or for more solid

    =SUMPRODUCT(A2:A9,D2:D9,--(A2:A9<>""))

    =SUMPRODUCT(A2:A9,D2:D9,--(B2:B9<>""))

  3. #3
    Registered User
    Join Date
    07-03-2010
    Location
    Lithuania
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

    Quote Originally Posted by Bo_Ry View Post
    Please try

    =SUMPRODUCT(A2:A9,D2:D9)

    or for more solid

    =SUMPRODUCT(A2:A9,D2:D9,--(A2:A9<>""))

    =SUMPRODUCT(A2:A9,D2:D9,--(B2:B9<>""))

    Wow, didn't expect such short formula. It works.
    In what scenarios third member --(A2:A9<>"") can be useful? Yes, it checks whether column is blank or not, but basic formula also works the same if the column is blank.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to sum multiplies of 2 columns, but only of their every 4th rows in one formula?

    --(A2:A9<>"") not useful

    --(B2:B9<>"") could help if you have a number in A3 and don't want to sum that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 11-14-2016, 07:39 PM
  2. Replies: 6
    Last Post: 09-16-2015, 09:47 AM
  3. [SOLVED] Formula that multiplies only every year
    By tryingtoexcelatexcel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-08-2015, 05:35 AM
  4. Replies: 5
    Last Post: 06-08-2015, 03:42 AM
  5. Replies: 2
    Last Post: 12-19-2013, 10:19 AM
  6. IF formula that also multiplies
    By amy22x3 in forum Excel General
    Replies: 5
    Last Post: 10-10-2007, 09:36 AM
  7. Formula that multiplies X for every Y unit
    By Dholden1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2007, 06:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1