VLOOKUP and COUNTIFS
Hi all,
Hoping to get some help with a LOOKUP/COUNTIF formula. I'm trying to create a grid that counts issue location by month. I've created this column in the source sheet which is the range for the VLOOKUP.
The sheet that this information needs to be pulled to:
I'm struggling to create a formula that will count each possible combination of month & location from the range.
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions
Best Answers
-
Hey @shelbylund,
Just to clarify, the month is listed in the same cell as the issue? Could the cell data be split so the Month is in one column and the issue is in another? Then from there use COUNTIFS to get the count of each issue for each month?
I only suggest that because the way the data is constructed currently requires a formula with a bit of text parsing, which makes formula construction/troubleshooting a bit tricky.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
Got it, I would try something like:
=COUNTIFS([Issue Location]:[Issue Location], "Cath/IR & CP Restock Staff (b)", [Month Reported]:[Month Reported], Month@row)
If this grid is on a separate sheet the ranges will need to reference the sheet column in place of the ranges I put in [brackets].
Answers
-
Where is the month being recorded?
-
Hey @shelbylund,
Just to clarify, the month is listed in the same cell as the issue? Could the cell data be split so the Month is in one column and the issue is in another? Then from there use COUNTIFS to get the count of each issue for each month?
I only suggest that because the way the data is constructed currently requires a formula with a bit of text parsing, which makes formula construction/troubleshooting a bit tricky.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
@bisaacs and @Dakota Haeffner, thanks for your help! I do have the Month & Issue Location in separate columns, as well. How would I write the COUNTIFS formula using these two columns?
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions -
Hey @shelbylund,
I would write it like so:
=COUNTIFS([Month Reported:[Month Reported], "May", [Issue Location]:[Issue Location], CONTAINS("Cath/IR", @cell)
You'll want to change the cell ranges to sheet references from the source sheet (and change the month/issue location to match the cell the formula is in), but this structure should work!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
Got it, I would try something like:
=COUNTIFS([Issue Location]:[Issue Location], "Cath/IR & CP Restock Staff (b)", [Month Reported]:[Month Reported], Month@row)
If this grid is on a separate sheet the ranges will need to reference the sheet column in place of the ranges I put in [brackets].
-
Shelby/Bee Lund | they/them/theirs
Oregon Health & Science University
Project Coordinator, Conversions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!