COUNTIF AND for data in 2 columns

Diana NBCU

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!

bisaacs

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!

Will Jeffords

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:

1. Click "Analyze Data" icon on far right-hand toolbar
2. Prompt; "How many Devices of each type are in each Location?" [it works its magic, spits out the chart]
3. Hover over any Location and Device part of the chart to see the number of devices!

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

Diana NBCU

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

Diana NBCU

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.

Nick Korna

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:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

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!