I have 5 different locations and 10 different types of devices in 2 different columns of my spreadsheet. I need get a couple of devices in each of the different locations (i.e. I have 40 various devices in 5 locations - I need to know how many of each device is in each location). That's an example, there are actually hundreds of devices in each location or I would just count them up.
Thank you!
Hey @Diana NBCU,
Could you provide a screenshot of the columns you have so we can see how the data is laid out. Please be sure to remove/hide any sensitive data.
Thanks!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Hi Diana! Woo-hoo, time to use new "Analyze Data" feature of Smartsheet!
I made some dummy data to test your scenario:
Then I did 3 things shown on next image:
I'm answering you this way, as I find myself using this super-cool feature all. the. time., but if you still need a formulaic way to do this without the AI stuff, let me know and I can help with that too!
Best,
Will
@Will Jeffords , YES! Thank you, this is what I was trying to explain - I'm going to give this a shot right now! Thanks so much!
As an example for the data above - I need to know of the various IDF locations, how many of each device runs to the IDF?
You can see I've figured out how to do 'total's but I need to break down the number of each device at every IDF.
Unfortunately you can't reference the header in the formula, but you would use a COUNTIFS in combination with some cross sheet references. So for your first column (PSER.122):
=COUNTIFS({Device Type},[Metric Type]@row,{IDF/SIDF/PSER Location - PTI},"PSER.122")
You can then use this as a column formula or drag down as preferred.
You would need to set up the cross sheet references here (no straight copy/paste), if you've not done these before then this should help:
Alternatively you could add the header values on the first row and then used a fixed value in place of typing - the choice is yours.
Hope this helps, but if I've misunderstood what you're trying to do or you have any problems/questions then let us know!
