SHEET SUMMARY FORMULA: SUM up inventory per account and associate

Marilen.Navarro103391

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

Genevieve P.

Hi @Marilen.Navarro103391

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

Marilen.Navarro103391

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.

Genevieve P.

Hi @Marilen.Navarro103391

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?

Genevieve P.

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

Marilen.Navarro103391

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

Genevieve P.

Hi @Marilen.Navarro103391

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

Marilen.Navarro103391

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.

Genevieve P.

Hi @Marilen.Navarro103391

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?

Marilen.Navarro103391

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

Genevieve P.

You're very welcome!!! 🙂