VLOOKUP and COUNTIFS

shelbylund

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.

bisaacs

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.

Dakota Haeffner

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

Dakota Haeffner

Where is the month being recorded?

bisaacs

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.

shelbylund

@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?

bisaacs

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!

Dakota Haeffner

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

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