# COUNTIFS - Referencing a Table with Multiple Criteria

#### mediatrap

##### New Member
Hello!

I work in a personnel office and need a spreadsheet to track specific individual data. I'm mostly using the COUNTIFS formula as it allows multiple criteria to be referenced against.

Here is the first formula that is working as it was intended:

=COUNTIFS(Everyone3[[#All],[Region/Section]],"SOD",Everyone3[[#All],[Urinalysis_Date]],"<"&CategoryLists!R2C10)

Part 1
Everyone3 is the table (range), Region/Section is the column of said table, "SOD" is the text inside to count
Part 2
Everyone3 is the table (range), Urinalysis_Date is the column of said table, "<" references to a date on a category list on another sheet

The PREVIOUS formula works as intended, but I cannot get THIS one to work:

=COUNTIFS(Everyone3[[#All],[Region/Section]],”SOD”,Everyone3[[#All],[Exempt/Profile]],”=Exempt/Profile”)

I know that something is wrong, I have theories as to what it is, but nothing seems to work. Any ideas?

Thanks for the time!

V/r,

MediaTrap

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Not 100% sure but do you need to seperate the = sign from the text?

=COUNTIFS(Everyone3[[#All],[Region/Section]],”SOD”,Everyone3[[#All],[Exempt/Profile]],"=""Exempt/Profile")

I can't help but notice the quotes are slightly different between the 2 formulas "SOD" & ”SOD” for example.

I had a colleague who for the life of him couldn't figure out what was wrong and it was the second style of slanted quotes that were causing it. Maybe try deleting these quotes in Excel and re-entering them manually?

EDIT: If I remember correctly the slanted quotes crept in because the formula had been copied from an Outlook email. Outlook used the wrong quotes style and the formula errored because of it.

Last edited:
Formulas, in particular non-working formulas, fail more often not in conveying the intent... That said, a guess:

=COUNTIFS(Everyone3[[#All],[Region/Section]],”SOD”,Everyone3[[#All],[Exempt/Profile]],”=”)

Formulas, in particular non-working formulas, fail more often not in conveying the intent... That said, a guess:

=COUNTIFS(Everyone3[[#All],[Region/Section]],”SOD”,Everyone3[[#All],[Exempt/Profile]],”=”)

I've tried everything mentioned by you and the individuals above, also with variation and have had no luck. They're all excellent ideas...I'm lost.

Hopefully this can be resolved soon. I have a deadline haha

Thanks for the help guys, I'll keep trying.

V/r,

MediaTrap

I've tried everything mentioned by you and the individuals above, also with variation and have had no luck. They're all excellent ideas...I'm lost.

Hopefully this can be resolved soon. I have a deadline haha

Thanks for the help guys, I'll keep trying.

V/r,

MediaTrap

Why don't you tell in words what you need?

Why don't you tell in words what you need?

I explained the first formula and hoped that it would be enough to reference the next. I'll try again lol, sorry.

=COUNTIFS(Everyone3[[#All],[Region/Section]],”SOD”,Everyone3[[#All],[Exempt/Profile]],”=Exempt/Profile”)

I need the formula to count all of the individuals under the region/section column titled "SOD" who are considered to be "Exempt/Profile" in the Exempt/Profile column.

Basically, I'm making the range the entire table so I don't have to worry about extending the ranges in the formula later. Of course I could just reference the range as the cells, but it seems to not Auto-update when i add new rows. Does that make sense? I'm sure I sound quite crazy.

The original formula with set ranges looked like this, but it's not auto/extending the ranges when I add new rows under said region/section & exempt/profile columns.

=COUNTIFS(Folder_PIF_Tracker!R2C1:R190C1,"SOD",Folder_PIF_Tracker!R2C16:R190C16,"Exempt/Profile")

The SHEET is called "Folder_PIF_Tracker", whereas the TABLE in the previous formula is called "Everyone3".

I understand this is all convoluted, but it works when it has set ranges and I'm not trying to finnagle it to work for the whole TABLE. THANKS!

Last edited:
Convert the table into an ordinary range.

Now state in words what calculation you need referring to the ordinary range. Do not refer to any formula or function. Avoid a lengthy description while doing so.

Convert the table into an ordinary range.

Now state in words what calculation you need referring to the ordinary range. Do not refer to any formula or function. Avoid a lengthy description while doing so.

I need to count all of the individuals listed on rows in the region/section column named "SOD" & if they're "Exempt/Profile" under that respective column. 2 conditions must be met to count.

I made this example, it might give you an idea of whats going on.
 Region/Section Last, First Exempt/Profile SOD Bird, Larry Exempt/Profile SOD Hanks, Tom N/A SOD Williams, Hank N/A SOD Raitt, Bonnie Exempt/Profile

<tbody>
</tbody>

I need to count all of the individuals listed on rows in the region/section column named "SOD" & if they're "Exempt/Profile" under that respective column. 2 conditions must be met to count.

I made this example, it might give you an idea of whats going on.
 Region/Section Last, First Exempt/Profile SOD Bird, Larry Exempt/Profile SOD Hanks, Tom N/A SOD Williams, Hank N/A SOD Raitt, Bonnie Exempt/Profile

<tbody>
</tbody>

=COUNTIFS(\$A\$1:\$A\$5,"SOD",\$C\$1:\$C\$5,"Exempt/Profile")

Or, if the range is converted into an Excel table:

=COUNTIFS(Table2[Region/Section],"SOD",Table2[Exempt/Profile],"Exempt/Profile")

Replies
14
Views
875
Replies
0
Views
213
Replies
6
Views
519
Replies
8
Views
279
Replies
1
Views
371

1,203,524
Messages
6,055,904
Members
444,832
Latest member
bgunnett8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back