# Help with Sum, Index and Match function

#### msharma864512

##### Member
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.

#### Attachments

• 15.7 KB Views: 4

#### NARAYANK991

##### Excel Ninja
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

#### Attachments

• 16.8 KB Views: 8

#### Peter Bartholomew

##### Well-Known Member
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.

#### Attachments

• 24.5 KB Views: 6

#### msharma864512

##### Member
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

##### Member
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

##### Excel Ninja
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

##### Active Member
Hi @msharma864512 ,

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

PFA Solution

Regard
Rahul shewale

#### Attachments

• 41.7 KB Views: 0