COUNTIF combined with MONTH

Barbara Witt

Hi there, I am trying to combined a COUNTIF with a MONTH=12. Trying to count whenever the date is in December.

I got this formula set up, but it comes back as O and I know I have 12.

=COUNTIFS({DAX Engagement Tracking Project Number}, <>" ", {DAX Ambient Inventory Actual Install Date}, MONTH(@cell) = 12, {DAX Ambient Inventory Actual Install Date}, YEAR(@cell) = 20)

So, I simplified the formula to only count how many dates I have that match December. It comes back INVALID DATA Type.

=COUNTIF({DAX Engagement Tracking Ship date}, MONTH(@cell) = 12)

Any thoughts on where I am not following the correct SmartSheet formula language?

Thanks so much, Barbara

Kelly Moore

Try this

=COUNTIFS({DAX Engagement Tracking # of Ambient sent}, VALUE(@cell)>0)

sometimes values look like values but behave like text. The VALUE function forces them to behave like a numeric value

oh, and what formula is your summary sheet using?

Malaina Hudson

Does every project ship only one device, or do you have multiple devices for projects? If you have multiple devices for project, you may need to use an =SUMIFS function instead of =COUNTIFS. COUNTIF will count occurrences; SUMIF will total the values. The syntax is almost identical, so if you have countif working, you're close to solving if the projects have multiple devices.

Malaina Hudson

There might be a better way to do this in a combined formula, but here's how I manage this in my projects.

I insert a helper column called Month and use the formula MONTH formula to return the month value to the helper column.

Then, have a numeric flag for the appropriate month near my summary, and use the COUNTIF as follows:

=COUNTIF(Month:Month, MoFlag@row)

When you're selecting a range in the COUNTIF, you have to define the top and bottom of the range by naming the column on each side of the colon, which is the range indicator.

I prefer the helper column method in my projects because it's more intuitive for the other users who access my sheets, since most of them are basic editors and not comfortable with compound formulas. YMMV with your audience.

Kelly Moore

Hey Barbara

I also frequently use @Malaina Hudson method with the month number helper column.

Looking at your original question-

I believe you have an extra space in your <>"" term. Also, since date functions are prone to be finnicky, try this (Note that YEAR returns the 4-digit value)

=COUNTIFS({DAX Engagement Tracking Project Number}, <>"", {DAX Ambient Inventory Actual Install Date}, IFERROR(MONTH(@cell),0) = 12, {DAX Ambient Inventory Actual Install Date}, IFERROR(YEAR(@cell),0) = 2020)

-Kelly

Barbara Witt

As always, @KDM, thanks so much for your assistance. I was hoping for an easy formula.....but I understand. LOL

The formula is to count if the project number is not empty and the Month is 12 and the Year is 2020. In reality, and I missed to note that correctly, it's supposed to count the number of devices that were shipped. Right now it counts the number of times there is the month December.

=COUNTIFS({DAX Engagement Tracking # of Ambient sent}, >0, {DAX Engagement Tracking Project#}, <>"", {DAX Engagement Tracking Ship date}, IFERROR(MONTH(@cell), 0) = 12, {DAX Engagement Tracking Ship date}, IFERROR(YEAR(@cell), 0) = 2020)

Unfortunately it won't do it. Thoughts?

Kelly Moore

Did the original formula correctly count the number of non-blank project numbers in December 2020? If yes, then we just have to get the correct syntax for the criteria you just added. If no, we need to back up and find why the other criteria isn't working as expected.

Barbara Witt

@KDM Yes, it did. I had one December date and it had a project number. I messed up: I don't need the count of project numbers, I need the count of devices.

Kelly Moore

I built your formula in my test sheet and it worked. When I'm stumped like this, I begin by removing the criteria pairs from the COUNTIFS formula one by one. Or, I build the formula one by one. Does this return values

=COUNTIFS({DAX Engagement Tracking # of Ambient sent}, >0)

Barbara Witt

@KDM Correct, Kelly. I found the error. I have not solved it yet, but I found it.

This is exactly here my challenge is. When looking at my Tracking spreadsheet and I count manually I come up with 39 devices. My sheet summary shows 39 devices. But when I pull them into my metrics spreadsheet with a COUNTIF, then it counts only 3 devices.

That is very strange. I'll look at it again tomorrow.

Kelly Moore

Try this

=COUNTIFS({DAX Engagement Tracking # of Ambient sent}, VALUE(@cell)>0)

sometimes values look like values but behave like text. The VALUE function forces them to behave like a numeric value

oh, and what formula is your summary sheet using?

Malaina Hudson

Does every project ship only one device, or do you have multiple devices for projects? If you have multiple devices for project, you may need to use an =SUMIFS function instead of =COUNTIFS. COUNTIF will count occurrences; SUMIF will total the values. The syntax is almost identical, so if you have countif working, you're close to solving if the projects have multiple devices.

Barbara Witt

Hi Malaina @Malaina Hudson, yep, that was it. I should have used SUMIF instead of COUNTIF. All fixed. Thanks so much.

Good morning @KDM Kelly, yep, checking the value as number did the trick. Thanks so much.

Wishing you both a wonderful weekend. It's Friday :>)

Malaina Hudson

@Barbara Witt Great! I'm glad that worked for you!