Request for formula

Good evening, I am looking for a formula to achieve the following:

Column 1 name: login

Column 2 name: supply type

There will be multiple login names within column 1, if there is a repeat login I would like to count the matches in column 2.

Example is listed below, login and supply type would be auto filled by a form and on the right side (which will actually be on a separate spreadsheet/summary page would show a count based upon the login. Hopefully that makes sense. I am looking to consolidate all supplies requested by a certain login providing me with a total count of supplies requested. Thanks in advance!


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @jtr8151

    An easier way to get this information is to create a report. That would dynamically update the login names on your second sheet - which otherwise you will need to keep adding.

    You will create a report based on your first sheet (the one with the form). You'll add the Login and Supply type columns. Filter where Login is NOT blank. Group by Name, then by Supply Type. Summarize by Supply type

    But giving you the formulas you asked for. On the second sheet, you will need to have the names added

    You will create cross sheet references by choosing the Reference Another Sheet from the formula window

    Badge

    =COUNTIFS({first sheet login column}, [login_total]@row, {first sheet supply type column}, "badge")

    change out the word between the quotes for each of the respective columns. It must be an exact match, case sensitive

    Will either of these approaches work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @jtr8151

    An easier way to get this information is to create a report. That would dynamically update the login names on your second sheet - which otherwise you will need to keep adding.

    You will create a report based on your first sheet (the one with the form). You'll add the Login and Supply type columns. Filter where Login is NOT blank. Group by Name, then by Supply Type. Summarize by Supply type

    But giving you the formulas you asked for. On the second sheet, you will need to have the names added

    You will create cross sheet references by choosing the Reference Another Sheet from the formula window

    Badge

    =COUNTIFS({first sheet login column}, [login_total]@row, {first sheet supply type column}, "badge")

    change out the word between the quotes for each of the respective columns. It must be an exact match, case sensitive

    Will either of these approaches work for you?

    Kelly

  • jtr8151
    jtr8151 ✭✭

    Thanks again for another great answer Kelly! Appreciate it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!