Help with Sum, Index and Match function

[msharma864512]

Hi,

I have data in sheet 1 where I have Revenue, EBITDA and COGS as 3 matrices. In sheet 2, I have created a drop down wherein I have all the 3 matrices and I need to pull the sum of these matrices. But i am only getting the revenues for the first company. Please help.

 

[NARAYANK991]

Hi ,

Enter this in cell C7 ; copy across and down.

=SUMIFS(INDEX(Sheet1!$C$4:$Q$23, ,MATCH($B$3, Sheet1!$C$2:$Q$2, 0) + MATCH(G$6, Sheet1!$C$3:$Q$3, 0) - 1), Sheet1!$B$4:$B$23,$B7)

Narayan

 

[Peter Bartholomew]

I have attached a file that is intentionally different from traditional Excel practice though it uses functionality that forms a perfectly standard part of Excel. I now aim to develop solutions that are built on the new dynamic arrays but here I have attempted to revert one of the solutions to use the older CSE array constructs.

 

[msharma864512]

Hi ,

Enter this in cell C7 ; copy across and down.

=SUMIFS(INDEX(Sheet1!$C$4:$Q$23, ,MATCH($B$3, Sheet1!$C$2:$Q$2, 0) + MATCH(G$6, Sheet1!$C$3:$Q$3, 0) - 1), Sheet1!$B$4:$B$23,$B7)

Narayan

Thank you so much. Could you tell what is the relevance of doing "-1" in this formula?

 

[msharma864512]

I have attached a file that is intentionally different from traditional Excel practice though it uses functionality that forms a perfectly standard part of Excel. I now aim to develop solutions that are built on the new dynamic arrays but here I have attempted to revert one of the solutions to use the older CSE array constructs.

Thanks for your help Peter

 

[NARAYANK991]

Thank you so much. Could you tell what is the relevance of doing "-1" in this formula?

Hi ,

The first MATCH function will return 1 if it finds a match in the first column ; so will the second MATCH function ; when we add these two results , we will get 2 , which is not the correct result.

Of course here the second row of headers , row #3 , repeats the same values thrice , which is why we can use the full range C3:Q3 in both MATCH functions ; if the values in C3:G3 did not repeat , we would need to change the reference in the second MATCH function.

Narayan

 

[rahulshewale1]

Hi @msharma864512 ,

If you are interested in Pivot Table with help of Power Query Solution ?

PFA Solution

Regard
Rahul shewale