1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Generate summary report from data using formula

Discussion in 'Ask an Excel Question' started by Samadhan Gaikwad, Jul 14, 2017.

  1. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    118
    HI,

    I have attached data from where I have to match two columns and count the vertain value which may be occur across various columns. Please find attached file.
    I want create report which I added into "Summary" sheet, wanted to find values in table.

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Try........

    In D2, formula copy across and down :

    =IFERROR(1/(1/SUMPRODUCT(--(OFFSET(Data!$C$1,MATCH(1,(Data!$A$2:$A$64=$C2)*(Data!$B$2:$B$64=$A$2),0),,,3)=D$1))),"")

    Regards
    Bosco

    Attached Files:

  3. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    118
    Thank you so much, Bosco, works very well. I think I can modify formula to look into more columns changing width arguments.
  4. Haseeb A

    Haseeb A Active Member

    Messages:
    364
    Hello Gaikwad,

    OFFSET is one of the powerful/flexible function & also a volatile.

    Here is another way with OFFSET.

    =SUM(COUNTIFS(Data!$A:$A,$C2,Data!$B:$B,$A$2,OFFSET(Data!$C:$C,,{0,1,2}),D$1))
  5. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    118
    Thank you, Haseeb. But now I want to look into more columns for the sum from 3 to 161. What will be the formula?
  6. Haseeb A

    Haseeb A Active Member

    Messages:
    364
    Hello Gaikwad,

    Sorry for the late response.

    Don't know about the structure of your file. using OFFSET to 161 columns might be performance issue.

    One way:

    =SUMPRODUCT(COUNTIFS(Data!$A:$A,$C2,Data!$B:$B,$A$2,OFFSET(Data!$C:$C,,COLUMN($A$1:INDEX($1:$1,161))-1),D$1))

    Change red highlighted to your #of columns.
  7. Samadhan Gaikwad

    Samadhan Gaikwad Member

    Messages:
    118
    Thank you, it's working fine, but little perf. issue.
  8. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    370
    Hi, to all!

    Another option without Volatile function:
    =SUMPRODUCT(--(Data!$A$2:$A$64=$C2),--(Data!$B$2:$B$64=$A$2),MMULT(--(Data!$C$2:$E$64=D$1),ROW(A$1:INDEX($A:$A,3))^0))

    Or, without zeros:
    =IFERROR(1/(1/SUMPRODUCT(--(Data!$A$2:$A$64=$C2),--(Data!$B$2:$B$64=$A$2),MMULT(--(Data!$C$2:$E$64=D$1),ROW(A$1:INDEX($A:$A,3))^0))),"")

    P.D: Change blue part to your number of columns.

    If you want full dynamic formula, try:
    =IFERROR(1/(1/SUMPRODUCT(--(Data!$A$2:$A$64=$C2),--(Data!$B$2:$B$64=$A$2),MMULT(--(Data!$C$2:$E$64=D$1),ROW(A$1:INDEX($A:$A,COUNTA($D$1:$F$1)))^0))),"")

    P.D.2: Adjust range D1:F1 to your titles.

    Blessings!

Share This Page