COUNTIFS - Referencing a Table with Multiple Criteria

mediatrap

New Member
Joined
Apr 5, 2013
Messages
5
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?:confused:

Thanks for the time!




V/r,

MediaTrap
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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")
 

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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]],”=”)
 

mediatrap

New Member
Joined
Apr 5, 2013
Messages
5

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

mediatrap

New Member
Joined
Apr 5, 2013
Messages
5

ADVERTISEMENT

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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

mediatrap

New Member
Joined
Apr 5, 2013
Messages
5
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>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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/SectionLast, FirstExempt/Profile
SODBird, LarryExempt/Profile
SODHanks, TomN/A
SODWilliams, HankN/A
SODRaitt, BonnieExempt/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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,970
Messages
5,599,105
Members
414,287
Latest member
Rbwester

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?

Disable AdBlock

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
Top