Forum Discussion

trevor935's avatar
trevor935
Copper Contributor
Feb 06, 2024

xlookup

Hi All

I am really new to this so apologize but i am trying to add up a list of values based on 3 criteria's using the below formula and it is returning a value error but if i take out the 3 criteria it is returning the sum but for all of the values because i have removed the 3rd criteria, should also mention the first 2 criteria's are horizontal and the third is vertical hence the transpose bit in the formula any suggestions would be really great thank you   

 

=SUM(XLOOKUP(1,(Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(TRANSPOSE(Apr!$K$5:$K$24)=Sheet1!E2),Apr!$N$5:$AG$22))

Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 06, 2024

    trevor935 

    The TRANSPOSE must be wrong!  I suspect you should be using two XLOOKUPS.  The first restricts the search to a column and the second searches within it for the cell to return.

    = XLOOKUP(
    Apr!E2, 
    Apr!$K$5:$K$22, 
    XLOOKUP(
      1,
      (Apr!$N$2:$AG$2=Apr!A2)*(Apr!$N$4:$AG$4=Apr!$F$1),
      Apr!$N$5:$AG$22
    )
  )
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 06, 2024

    trevor935 

    How about

     

    =SUMPRODUCT((Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(TRANSPOSE(Apr!$K$5:$K$24)=Sheet1!E2),Apr!$N$5:$AG$22)

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 06, 2024

        trevor935 

        Try this then:

         

        =SUMPRODUCT((Apr!$N$2:$AG$2=Sheet1!A2)*(Apr!$N$4:$AG$4=Sheet1!$F$1)*(Apr!$K$5:$K$24=Sheet1!E2),Apr!$N$5:$AG$24)

         

        Please note that I used Apr!$N$5:$AG$24 to match the size of Apr!$K$5:$K$24

Resources