Formula

Nkeiruka. Aguocha

Hello again,

I need help with how to set up a formula for my dashboard. How do I create a formula that tells me how many Task and CAR's were issued per month? Please advise, thanks!

Kleerfyre

SUM formula. I commented on another post of yours with an example to solve this.

Kelly Moore

Hey @Nkeiruka. Aguocha

Your formula, as written, will work assuming (1) you are typing in the exact name of your "Task" in between the quotes and (2) you already have your {CAR Log Range 4} as a column of month numbers.

Depending on your real data in your Task column (from the screenshot above), you may be able to make your formula dynamic (not needing to type the Task in between the quotes) by changing your formula to:

=COUNTIFS({CAR Log Range 2}, Task@row, {CAR Log Range 4},=6) *This assumes Range 4 is a column of Month Numbers.

If your date range is not month numbers, try this:

=COUNTIFS({CAR Log Range 2}, Task@row, {CAR Log Range 4}, IFERROR(MONTH(@cell),0)=6)

This uses the MONTH() function to find the month number. The IFERROR will force the month number to zero if there are non-dates in the date range. It doesn't do anything if there isn't an error.

*If you do need to hard code in the Task names, replace the Task@row with the actual "Task" (insert between the quotes)

Will any of these work for you?

Kelly

Kleerfyre

SUM formula. I commented on another post of yours with an example to solve this.

Nkeiruka. Aguocha

I see it now. THanks so much for your help!!

Kleerfyre

https://community.smartsheet.com/discussion/comment/355318#Comment_355318

No problem! Glad I could help!

Nkeiruka. Aguocha

I'm so sorry to bug again,

I'm sure I am doing something wrong. I wanted to see the sum of how many tasks were issued in any given month. The below is what I have started with but was not sure how to complete it🙃. In my column that I am pulling the formula from, I have CAR or task on all the rows. I needed to know, from that column, how many task were issued in June, July and all the other months. The Months needed are in a separate column. Thanks!

=SUMIF({CAR Log Range 2}, "CAR")

Kelly Moore

Hey @Nkeiruka. Aguocha

Your formula, as written, will work assuming (1) you are typing in the exact name of your "Task" in between the quotes and (2) you already have your {CAR Log Range 4} as a column of month numbers.

Depending on your real data in your Task column (from the screenshot above), you may be able to make your formula dynamic (not needing to type the Task in between the quotes) by changing your formula to:

=COUNTIFS({CAR Log Range 2}, Task@row, {CAR Log Range 4},=6) *This assumes Range 4 is a column of Month Numbers.

If your date range is not month numbers, try this:

=COUNTIFS({CAR Log Range 2}, Task@row, {CAR Log Range 4}, IFERROR(MONTH(@cell),0)=6)

This uses the MONTH() function to find the month number. The IFERROR will force the month number to zero if there are non-dates in the date range. It doesn't do anything if there isn't an error.

*If you do need to hard code in the Task names, replace the Task@row with the actual "Task" (insert between the quotes)

Will any of these work for you?

Kelly

Nkeiruka. Aguocha

It did work for me...thank you so much Kelly!!