+ Reply to Thread
Results 1 to 9 of 9

Matrix formula to sum rows?

  1. #1
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    281

    Matrix formula to sum rows?

    Hi,

    I have this: index(some range,MATCH(1, (A1=F1:F10)*(B1=G1:G10)*(B1=H1:H10).... in A1 is date, in F1&10 are dates. Problem is in G,H,I,J columns. I must search given B1 value inside G1:J10 range. I must look for A1 date in F1&10 range and then SUM that row eg. G4:J4, and that value should be equal B1. I can just use helper column to sum G:J range. But, is there some kind great formula I would appreciate very much. THANKS!

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Matrix formula to sum rows?

    please attach a sample file with some data and explain how you want the result calculated?
    enter some results manually as example.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    281

    Re: Matrix formula to sum rows?

    I'm unable to upload 3,4MB sensitive data file.

    G1:J10 needs to be SUM by rows w/o helper column. And then (B1=1row:10row). It's simple, i think, it0's just.. i'm brainless right now

  4. #4
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    281

    Re: Matrix formula to sum rows?

    {=IF(ISBLANK(B85),"",INDEX('SH'!$N$8:$N$2150,MATCH(1,('MAIN'!B85='SH'!$B$8:$B$2150)*('MAIN'!C85='SH'!$C$8:$C$2150)*('MAIN'!D85='SH'!$D$8:$D$2150)*('MAIN'!E85='SH'!$E$8:$E$2150)*('MAIN'!E85='SH'!$AD$8:$AD$2150),0)+COUNTIFS($B$84:$B84,$B85,$C$84:$C84,$C85,$D$84:$D84,$D85,$E$84:$E84,$E85,$F$84:$F84,$F85)))}

    Sorry, This formula is on MAIN sheet in G85. Its searching in sheet SN in N8:N2150 for match B85, C85...... my brain

    AD is helper column, still it's isn't working.

  5. #5
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    281

    Re: Matrix formula to sum rows?

    MATCH not working with non-linked columns B, C, D, AD

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Matrix formula to sum rows?

    I'm unable to upload 3,4MB sensitive data file.
    A small fictionalized example may work.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Matrix formula to sum rows?

    We dont need your whole file, nor do we need your real data - just a small sample with realistic sample data
    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

  8. #8
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    281

    Re: Matrix formula to sum rows?

    In post #4 the problem was in COUNTIFS part. So sorry, took me so long time. Thanks everyone!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Matrix formula to sum rows?

    Glad that you found a solution. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Deleting rows (matrix formule)
    By martsanders in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-12-2016, 09:08 AM
  2. Rearrange rows in matrix (excel)
    By Pax2Garo in forum Excel General
    Replies: 1
    Last Post: 06-25-2015, 04:44 PM
  3. [SOLVED] Merging Columns and Rows of Data into a Matrix
    By DaveBre in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2014, 06:21 AM
  4. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  5. [SOLVED] Matrix into new rows
    By jennymc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 11:11 AM
  6. Replies: 1
    Last Post: 11-24-2008, 10:13 AM
  7. matrix columns to rows
    By J_J in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2005, 02:05 AM

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