+ Reply to Thread
Results 1 to 9 of 9

SUMPRODUCT using MOD to sum odd columns

  1. #1
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    173

    SUMPRODUCT using MOD to sum odd columns

    i am using this formula

    =SUMPRODUCT('Monthly View'!D4:AA5000*MOD(COLUMN('Monthly View'!D4:AA4),2))

    But it only does the even columns, it will not add up the odd columns as i just get all zeros in the Mod part of the formula or the even columns go to 1 not the odd columns

    Any help would be appreciated

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,605

    Re: SUMPRODUCT using MOD to sum odd columns

    Rather than show us a formula that doesnt work, upload a sample workbook and show us what you expect - then we wont need to guess
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,188

    Re: SUMPRODUCT using MOD to sum odd columns

    Perhaps

    =SUMPRODUCT('Monthly View'!D4:AA5000*(MOD(COLUMN('Monthly View'!D4:AA4),2)=0))

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,631

    Re: SUMPRODUCT using MOD to sum odd columns

    Alternative to MOD(): ISODD or ISEVEN:
    =SUMPRODUCT('Monthly View'!$D$4:$AA$5000*(ISODD(COLUMN('Monthly View'!$D$4:$AA$4))))
    Ben Van Johnson

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,765

    Re: SUMPRODUCT using MOD to sum odd columns

    May be this

    =SUMPRODUCT('Monthly View'!D4:AA5000*MOD('Monthly View'!D4:AA4,2))
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,631

    Re: SUMPRODUCT using MOD to sum odd columns

    @kvsrinivasamurthy:

    Note: "SUMPRODUCT using MOD to sum odd columns"
    Your formula calculates based on odd VALUES in row 4 cells vs. odd columns, i.e., E,G,...
    Last edited by protonLeah; 05-26-2019 at 12:55 AM.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,765

    Re: SUMPRODUCT using MOD to sum odd columns

    UltimateNeo says

    But it only does the even columns, it will not add up the odd columns

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,188

    Re: SUMPRODUCT using MOD to sum odd columns

    @kvsrinivasamurthy

    If all of the values in the cells are even then your suggestion will not add up any columns!

    Note that the thread title and the question state odd columns, not odd values!

  9. #9
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    173

    Re: SUMPRODUCT using MOD to sum odd columns

    I tried the formula in a new file and it works =SUMPRODUCT('Monthly View'!D4:AA5000*(MOD(COLUMN('Monthly View'!D4:AA4),2)=0))

    thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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