# Need help with complicated formula

Options
✭✭✭✭

As usual, Im having a hard time developing a complicated formula. @Paul Newcome helped me last time with a great solution. Im hoping someone can help untangle the webs for me.

I have a sheet that has (among other things) a Date column, another column with a formula that determines a number of days, and a month number helper column.

What I need to do is to count the 9th column and break it into 2 buckets (columns) per month on another sheet.

The other sheet will look similar to this:

Month Bucket1 (if number is 1-10) Bucket2 (if number if 11+)

Jan X X

Feb X X

Mar X X

Im trying to figure out how to fill in the numbers for each bucket.

• ✭✭✭✭✭✭
Options

Exactly which column are you trying to break down into the buckets?

• ✭✭✭✭
Options

The column that shows the number 12.

The column that has the green 12 is a formula. The column to the right of it is really just a duplicate that I can get rid of. As you can see some of the rows have a value and some do not. I am only concerned with the ones that have a value.

• ✭✭✭✭✭✭
Options

Ok. And can you provide a screenshot with manually entered data that shows exactly what you are trying to accomplish?

• ✭✭✭✭
Options

Hope this makes it more clear. :)

• ✭✭✭✭✭✭
Options

Ok. So how exactly did you determine the number 4 goes in the 1-10 Bucket column and the number 3 goes n the 11+ Bucket column?

• ✭✭✭✭
edited 07/22/20
Options

There are 4 rows in the Average Time column that are between 1-10.

There are 3 rows in the Average Time column that are between 11+

There are 7 total rows with numbers in that column.

I need to capture these counts per month.

• ✭✭✭✭✭✭
Options

Oh. So you are just trying to get a count. Try this...

=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 6, {Number Column}, AND(@cell <> "", @cell <= 10))

• ✭✭✭✭
Options

Hi Paul,

I think that is working. Need to do some more checking.

Would you mind explaining what this means? This is similar to the past formula you helped me with, which I tried to make use of before posting here, but I just dont understand what this means.

Count date_column if month=6, AND number_column is not blank and <=10

Is this correct?

• ✭✭✭✭✭✭
Options

That is correct. The only reason for the AND function is to combine the criteria for the second range so that we do not have to repeat the same range multiple times. It helps me personally keep things organized in really long formulas so that I can make sure I didn't miss anything.

• ✭✭✭✭
Options

Cool. Thanks so much.

Glad to know I was on the right track before posting my question. It seems to be working great!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!