Announcement

Collapse
No announcement yet.

SUMPRODUCT use to return a text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUMPRODUCT use to return a text



    If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value?

    For example:

    COL A COL B COL C
    A 1 X
    A 2 F
    A 3 G
    B 1 E
    B 2 S


    I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F"
    =SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).

    Does anyone know how to fix this?
    Thanks
    Last edited by TPK; May 11th, 2006, 23:09.

  • #2
    Re: SUMPRODUCT use to return a text

    Hi, this will only work for unique occurances.
    The result of the SUMPRODUCT is used as the row argument in the INDEX formula

    =INDEX($C$1:$C$5,SUMPRODUCT( ($A$1:$A$5="A")*($B$1:$B$5=2)*ROW($B$1:$B$5)),1)
    Last edited by Jack in the UK; May 12th, 2006, 02:42.

    Cheers
    Andy

    Comment


    • #3
      Re: SUMPRODUCT use to return a text

      By design, SUMPRODUCT is just not going to allow you to return text. It's intended to multiply arrays. You'll have to be slightly more creative to come up with a way to return text using a SUMPRODUCT.

      One solution would be to use SUMPRODUCT to return a row number for the text you wish to return:
      =SUMPRODUCT( (D6:D10="A")*(E6:E10=2), ROW(F6:F10)-1 )

      With a row number, you can use the OFFSET function to return the text:
      =OFFSET(F1, SUMPRODUCT( (D6:D10="A")*(E6:E10=2), ROW(F6:F10)-1 ),0 )


      If I noodled on it a bit more I might be able to come up with something sleeker... maybe using an INDEX or something... but you get the idea.
      Sub All_Macros(Optional control As Variant)

      Comment


      • #4
        Re: SUMPRODUCT use to return a text

        Originally posted by Andy Pope
        Hi,

        This will only work for unique occurances.
        The result of the SUMPRODUCT is used as the row argument in the INDEX formula
        =INDEX($C$1:$C$5,SUMPRODUCT( ($A$1:$A$5="A")*($B$1:$B$5=2)*ROW($B$1:$B$5)),1)

        Careful... I think this one's gonna choke if the range doesn't happen to start on row 1. You'd have to just modify it so ROW($B$1:$B$5) returns the row index within the range, rather than the actual row.
        Last edited by Aaron Blood; May 11th, 2006, 23:39.
        Sub All_Macros(Optional control As Variant)

        Comment


        • #5
          Re: SUMPRODUCT use to return a text

          Exactly what Andy and Arron have already said. Here is a slightly different formulation of the formula using INDEX and MATCH (which is an array formula that MUST be entered using Shift+Ctrl+Enter). (See column F in the attached)

          =INDEX($C$1:$C$5,MATCH($A1 & $B1,$A$1:$A$5 & $B$1:$B$5))
          Attached Files
          Last edited by thomach; May 11th, 2006, 23:40.
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6
            Re: SUMPRODUCT use to return a text

            Fair point Aaron.
            This should sort it
            =INDEX($C$1:$C$5,SUMPRODUCT(($A$1:$A$5="A")*($B$1:$B$5=2)*(ROW($B$1:$B$5)-ROW($B$1)+1)),1)

            Cheers
            Andy

            Comment


            • #7
              Re: SUMPRODUCT use to return a text

              Just an idea, but if you concatenated the A and B columns into column D, you could use VLOOKUP.
              .

              Comment


              • #8
                Re: SUMPRODUCT use to return a text

                Originally posted by Andy Pope
                Fair point Aaron.
                This should sort it
                =INDEX($C$1:$C$5,SUMPRODUCT(($A$1:$A$5="A")*($B$1:$B$5=2)*(ROW($B$1:$B$5)-ROW($B$1)+1)),1)

                Yeah... I didn't like that row math either. That's what made me opt for the OFFSET. But if we thought about it some more maybe something better would present itself.

                The offset, I don't care for the first cell ref...
                The index, I don't like the row math...
                Indirect? eh, not crazy about it...

                We might just be stuck with it.
                Last edited by Aaron Blood; May 11th, 2006, 23:46.
                Sub All_Macros(Optional control As Variant)

                Comment


                • #9
                  Re: SUMPRODUCT use to return a text

                  Originally posted by ByTheCringe2
                  Just an idea, but if you concatenated the A and B columns into column D, you could use VLOOKUP.
                  You're correct...

                  But you missed the discussion on the previous D function thread when the OP learned how to substitute the SUMPRODUCT. Looking for something more flexible than multi-concats. If there are several (SEVERAL) multi-criteria functions occuring, the concats start taking up way too much real estate.
                  Sub All_Macros(Optional control As Variant)

                  Comment


                  • #10
                    Re: SUMPRODUCT use to return a text

                    Originally posted by ByTheCringe2
                    Just an idea, but if you concatenated the A and B columns into column D, you could use VLOOKUP.
                    Minor nit. VLOOKUP requires the lookup table to have the looked-up value in the first column. Hence, the concatenated A&B columns would have to be inserted in a new column C (or the current column C data repeated after the new column D) for VLOOKUP to work.
                    Best Regards,
                    Tom
                    ---------------------------
                    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                    Comment


                    • #11
                      Re: SUMPRODUCT use to return a text

                      Thanks for the ideas everyone. Although not pretty I think the index idea will work for me. Thanks for the input!

                      Comment


                      • #12
                        Re: SUMPRODUCT use to return a text

                        Originally posted by thomach
                        Minor nit. VLOOKUP requires the lookup table to have the looked-up value in the first column. Hence, the concatenated A&B columns would have to be inserted in a new column C (or the current column C data repeated after the new column D) for VLOOKUP to work.
                        Who are you calling a minor nit!! LOL

                        Yes, good point.
                        .

                        Comment


                        • #13
                          Re: SUMPRODUCT use to return a text

                          Maybe...
                          Input formula in cell E1 and copy down.

                          =IF(SUMPRODUCT(--($A$1:$A$6=$A1),--($B$1:$B$6=$B1)),C1,"")
                          Attached Files
                          Last edited by Jack in the UK; May 12th, 2006, 02:28.

                          Comment


                          • #14
                            Re: SUMPRODUCT use to return a text

                            Originally posted by Fin Fang Foom
                            Maybe...

                            Input formula in cell E1 and copy down.
                            =IF(SUMPRODUCT(--($A$1:$A$6=$A1),--($B$1:$B$6=$B1)),C1,"")
                            Nah... looking for single cell solutions.
                            Last edited by Jack in the UK; May 12th, 2006, 02:28.
                            Sub All_Macros(Optional control As Variant)

                            Comment


                            • #15


                              Re: SUMPRODUCT use to return a text

                              Originally posted by Aaron Blood
                              Nah... looking for single cell solutions.
                              My mistake.
                              =INDEX($C$1:$C$5,MATCH(1,INDEX(($A$1:$A$5="A")*($B$1:$B$5=2),0,1),0))
                              Last edited by Jack in the UK; May 12th, 2006, 02:28.

                              Comment

                              Working...
                              X