COUNTIFS - Referencing a Table with Multiple Criteria

[mediatrap]

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

 

[Dryver14]

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]

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.

 

[Aladin Akyurek]

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]

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]

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]

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!

 

[Aladin Akyurek]

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]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region/Section
[/TD]
[TD]Last, First
[/TD]
[TD]Exempt/Profile
[/TD]
[/TR]
[TR]
[TD]SOD
[/TD]
[TD]Bird, Larry
[/TD]
[TD]Exempt/Profile
[/TD]
[/TR]
[TR]
[TD]SOD
[/TD]
[TD]Hanks, Tom
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]SOD
[/TD]
[TD]Williams, Hank
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]SOD
[/TD]
[TD]Raitt, Bonnie
[/TD]
[TD]Exempt/Profile
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region/Section[/TD]
[TD]Last, First[/TD]
[TD]Exempt/Profile[/TD]
[/TR]
[TR]
[TD]SOD[/TD]
[TD]Bird, Larry[/TD]
[TD]Exempt/Profile[/TD]
[/TR]
[TR]
[TD]SOD[/TD]
[TD]Hanks, Tom[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]SOD[/TD]
[TD]Williams, Hank[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]SOD[/TD]
[TD]Raitt, Bonnie[/TD]
[TD]Exempt/Profile[/TD]
[/TR]
</tbody>[/TABLE]

=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")