Formula
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!
Best Answers
-
SUM formula. I commented on another post of yours with an example to solve this.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
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
Answers
-
SUM formula. I commented on another post of yours with an example to solve this.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
I see it now. THanks so much for your help!!
-
No problem! Glad I could help!
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
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")
-
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
-
It did work for me...thank you so much Kelly!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 435 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
