SHEET SUMMARY FORMULA: SUM up inventory per account and associate
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
-
No worries!
If you're looking fro specific names, you can use the OR function and search for an exact phrase:
=SUMIFS(Inventory:Inventory, Associate:Associate, "JOSE CRUZ", Accounts:Accounts, OR(@cell = "SM CITY", @cell = "SM NORTH EDS", @cell = "SM MEGA MALL"))
This says that either the cell in that row = this specific text (ex. "SM CITY"), OR it equals this, OR it equals this.
Using CONTAINS was just trying to make your formula a bit shorter so you didn't have to type so much, but this will look for exact matches. You can then trade out whats "in these" for the other accounts.
Let me know if I can clarify further!
Cheers,
Genevieve
-
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.
-
I'm glad you got it!
Hmm, no, if the numbers are also a hyperlink then it won't be read as a number... see in this example I have made the 4 a hyperlink so then the total is 4 instead of 8 for Jose:
If this is your set up, you may want to create a "helper column" which just returns the Value from this Inventory column:
=VALUE(Inventory@row)
Then the Sheet Summary would look at the Value column instead of the Inventory column:
You can hide this column in your sheet and the formula will still run (see here on Hiding Columns).
Does that make sense?
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
-
No worries!
If you're looking fro specific names, you can use the OR function and search for an exact phrase:
=SUMIFS(Inventory:Inventory, Associate:Associate, "JOSE CRUZ", Accounts:Accounts, OR(@cell = "SM CITY", @cell = "SM NORTH EDS", @cell = "SM MEGA MALL"))
This says that either the cell in that row = this specific text (ex. "SM CITY"), OR it equals this, OR it equals this.
Using CONTAINS was just trying to make your formula a bit shorter so you didn't have to type so much, but this will look for exact matches. You can then trade out whats "in these" for the other accounts.
Let me know if I can clarify further!
Cheers,
Genevieve
-
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.
-
I'm glad you got it!
Hmm, no, if the numbers are also a hyperlink then it won't be read as a number... see in this example I have made the 4 a hyperlink so then the total is 4 instead of 8 for Jose:
If this is your set up, you may want to create a "helper column" which just returns the Value from this Inventory column:
=VALUE(Inventory@row)
Then the Sheet Summary would look at the Value column instead of the Inventory column:
You can hide this column in your sheet and the formula will still run (see here on Hiding Columns).
Does that make sense?
-
THANK YOU SO MUCH !!! I'M SO GLAD!!!
-
You're very welcome!!! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!