Search in another sheet for a value with sum function?!

Options

Hi community,

i have a problem and need your help.

I have to sheets "main" and "Input"

In "main" I use the formula vlookup to search for a string that is in both "main" and the "input" sheet. if the string is found in "input", I return the value amount to the main sheet. This formular is working without any problems.

Formular for that:

=IFERROR(VLOOKUP(String@row; {Import Bereich 2}; 2; false); "")

 

Now to my problem:

It can happen that the string appears a few times in the "input" sheet. Then I want to sum the values "amount" for the string and give it back to the main sheet.

For the example below it should like this:

43FA123456 --> 44

43FA987654 --> 33

43FA112233 --> 21

sheet "main" has approx 5000 lines

Sheet "input" has approx 2000 lines.

 

Do you have an idea how to solve the issue?

 

Regards

Christian

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hello @ChrisNHS

    If I understand correctly, instead of the vlookup you are currently performing, you would prefer to receive the Sum, based on your string.

    =SUMIFS({Input sheet Amount Column}, {Input Sheet String column}, String@row)

    If desired, you can wrap this formula in an IFERROR.

    Will this work for you?

    Kelly

  • ChrisNHS
    Options

    Yes, that looks very good!

    I was on the wrong path. I tried to solve this with the Vlookup function

    Thank you Kelly.


    Christian

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!