• 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...

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

Help with Sum, Index and Match function

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

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

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?
 
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
 
Top