# SHEET SUMMARY FORMULA: SUM up inventory per account and associate

Options
edited 06/14/20

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!!!!

Tags:

Options

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.

Options

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

• Options

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

Options

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.

• Options

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