VLOOKUP and COUNTIFS

Options

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

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Answer ✓
    Options

    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

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Options

    Where is the month being recorded?

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

  • shelbylund
    Options

    @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

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    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!

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Answer ✓
    Options

    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].

  • shelbylund
    Options

    @bisaacs and @Dakota, Thank you so much! This worked great.

    Shelby/Bee Lund | they/them/theirs
    Oregon Health & Science University
    Project Coordinator, Conversions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!