SHEET SUMMARY FORMULA: SUM up inventory per account and associate

Marilen.Navarro103391
Marilen.Navarro103391 ✭✭✭✭✭
edited 06/14/20 in Formulas and Functions

Hello,

I need help in formulating my Sheet Summary.

On my sheet I have number of Associates with their corresponding accounts. I need to sum up the inventory per account. Ex : Jose Cruz has 3 accounts in color violet and 8 inventory.

Need to show this:

JOSE CRUZ | SM CITY/ SM NORTH EDSA /SM MEGA MALL | 8

JOSE CRUZ | PUREGOLD | 5

JOSE CRUZ | ROBINSONS / ROBINSONS 1 | 5

NINOY AQUINO | AYALA MALL / AYALA MALL 1 | 1


THANKS!!!!

Best Answers

  • Marilen.Navarro103391
    Marilen.Navarro103391 ✭✭✭✭✭
    Answer ✓

    Thank you. I got it !!! I just have one last question....

    Can I use a sheet with a hyperlink. Say this example.. the column "inventory", the numbers are link to a URL?

    If not possible, can you advise a work around??

    Thank again.

Answers

  • Hi Marilen,

    You can use a SUMIFS formula (plural, with an S) to look for that specific name in the Associate Column, and then a CONTAINS function to search for a keyword in the Accounts Column.

    For example, this would be my formula for the first line, Jose Cruz, SM CITY/ SM NORTH EDSA /SM MEGA MALL:

    =SUMIFS(Inventory:Inventory, Associate:Associate, "JOSE CRUZ", Accounts:Accounts, CONTAINS("SM", @cell))


    To search for PURE GOLD, I would just change out what I'm looking for in the CONTAINS section:

    =SUMIFS(Inventory:Inventory, Associate:Associate, "JOSE CRUZ", Accounts:Accounts, CONTAINS("PURe GOLD", @cell))


    Then to search for a different person, change out the name in quotes:

    =SUMIFS(Inventory:Inventory, Associate:Associate, "NINOY AQUINO", Accounts:Accounts, CONTAINS("AYALA MALL", @cell))



    Please keep in mind that the CONTAINS function is looking for just that... if the cell contains certain words... so if anything else has SM in the name (like SMALL) then it would be included in the first formula. Make sure you are very specific in the words you're looking for.

    Let me know if this works for you, or if you have any questions!

    Cheers,

    Genevieve

  • Hello,

    Thank you.... But I need to sum up SM CITY, SM NORTH EDS AND SM MEGAMALL. THE SUM IS 0+4+4 = 8

    The name of account could be different not "SM" in the name.

    CONTAINS("SM CITY", "SM NORTH EDS"......... what's the right formula.... Thanks

  • Marilen.Navarro103391
    Marilen.Navarro103391 ✭✭✭✭✭
    Answer ✓

    Thank you. I got it !!! I just have one last question....

    Can I use a sheet with a hyperlink. Say this example.. the column "inventory", the numbers are link to a URL?

    If not possible, can you advise a work around??

    Thank again.

  • THANK YOU SO MUCH !!! I'M SO GLAD!!!

  • You're very welcome!!! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!