Generate summary report from data using formula

[Samadhan Gaikwad]

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.

 

[bosco_yip]

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

 

[Samadhan Gaikwad]

Thank you so much, Bosco, works very well. I think I can modify formula to look into more columns changing width arguments.

 

[Haseeb A]

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))

 

[Samadhan Gaikwad]

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))

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

 

[Haseeb A]

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.

 

[Samadhan Gaikwad]

Thank you, it's working fine, but little perf. issue.

 

[John Jairo V]

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!