averageif with multiple criteria

taylor.thompson89836

I am trying to find the average of time in position (demographics range 1)when the job class (demographics range 3) is manager or higher. I'm currently using =avg(collect to try to specify the multiple criteria but am getting a #divide by zero error message.

I have my formula written as =AVG(COLLECT({Demographics Range 1}, {Demographics Range 3}, "MG", {Demographics Range 3}, "VP"))

Can anyone tell me what I might be doing wrong?

L_123

Few things. First of all you should always name your ranges. There is no way anyone can understand what the references are just by reading the formula, and with good design they always should be able to. Second, you reference the same range twice inside of a collect without one of them being the return, this is generally a bad idea as it makes the design more complicated and especially on formula that reference other sheets can be very inefficient. Lastly the collect formula loves @cell references, if nothing seems to be working, toss a couple of those in there and see if it likes it better. Or even start by adding them, can't hurt.

=AVG(COLLECT({Demographics Range 1}, {Demographics Range 3}, or(@cell = "MG", @cell = "VP"))

The divide by zero could be caused by a few things, most commonly in my experience it is caused by uneven ranges. All ranges referenced inside of a collect formula must have the same amount of cells. Smartsheet has this fun little glitch where it likes to delete your ranges after you click away from them the first time, you should double check that they are referencing the correct cells/columns.

Sorry for the lecture, I just really like the collect formula, and have a lot sheets that function only because of it.

L_123

=countifs({Demographics Job Class}, or(@cell = "MG", @cell = "SM", @cell = "VP"),{Demographics Gender},@cell = "F"

L_123

Few things. First of all you should always name your ranges. There is no way anyone can understand what the references are just by reading the formula, and with good design they always should be able to. Second, you reference the same range twice inside of a collect without one of them being the return, this is generally a bad idea as it makes the design more complicated and especially on formula that reference other sheets can be very inefficient. Lastly the collect formula loves @cell references, if nothing seems to be working, toss a couple of those in there and see if it likes it better. Or even start by adding them, can't hurt.

=AVG(COLLECT({Demographics Range 1}, {Demographics Range 3}, or(@cell = "MG", @cell = "VP"))

The divide by zero could be caused by a few things, most commonly in my experience it is caused by uneven ranges. All ranges referenced inside of a collect formula must have the same amount of cells. Smartsheet has this fun little glitch where it likes to delete your ranges after you click away from them the first time, you should double check that they are referencing the correct cells/columns.

Sorry for the lecture, I just really like the collect formula, and have a lot sheets that function only because of it.

taylor.thompson89836

Thank you so much! That worked!

I also need to find how many Females are in the gender column, if their job class is MG, SM, or VP. I've tried a bunch of countifs variations and can't seem to find the right syntax.

Would you know the proper formula to find this as well?

L_123

=countifs({Demographics Job Class}, or(@cell = "MG", @cell = "SM", @cell = "VP"),{Demographics Gender},@cell = "F"