COUNTIFS - Not Counting

[hmltnangel]

OK, so I know this is going to be one of those reallllllly silly errors Im making. But I cant see the wood for the trees.

I have a COUNTIFS, that I am asking to count the mount of times an Employee ID matches, and the amount of times a value matches another cell.

The employee id matches fine, but its not counting the cell match value. So, I broke it down and said a simple formula like this.

=IF([@Goal]=$U$8,"LV","") This simply has a blank for every answer. I suspect its the way the [@GOAL] Value is written in $U$8 as per below, or maybe the number of character at 1129?

BLAH BLAH -

blah blahblah:
Blah 1- blah blah.
blah 2 - blah blah.
blah 3 - blah blah.
blah 4 - blah blah.
blah 5 - blah blah.
blah 6 - blah blah

ANy ideas?

 

[jasonb75]

If you're trying to make partial matches as your example suggests might be the case then you need to use wildcards in COUNTIFS, or SEARCH with a simple IF formula.

=COUNTIFS([Goal],"*"&$U$8&"*")

=IF(ISNUMBER(SEARCH($U$8,[@Goal])),"LV","")

 

[hmltnangel]

Thanks, but the problem is - it should be an exact match. Thats why im stumped.

If the value in goal is an exact match to U8 - then show LV

 

[hmltnangel]

The full original formula is

=IF([@Emplid]="","",SUM(COUNTIFS([Employee ID],[Emplid],[Goals Entered],"Incomplete")-COUNTIFS([Employee ID],[Emplid],[Goal],$U$8)))

and its not subtracting the examples where Goal matches U8

 

[jasonb75]

Noting that your profile says you're using office 2016, have you array confirmed the formula with Ctrl Shift Enter?

Alternatively, change SUM to SUMPRODUCT then it should work without array confirmation.

Note that you may need to use sumproduct twice to avoid conflicts between arrays.

=IF([@Emplid]="","",SUMPRODUCT(COUNTIFS([Employee ID],[Emplid],[Goals Entered],"Incomplete"))-SUMPRODUCT(COUNTIFS([Employee ID],[Emplid],[Goal],$U$8)))

 

[jasonb75]

Reading post #1 again, I notice that you mentioned 1129 characters? Countifs has a limit of 255 characters per cell so you will likely need an alternative to get the correct results.

Is your profile up to date in that it is showing office 2016, or are you now using a different version?

 

[hmltnangel]

Yeah still on 2016 at the minute and No I didnt array confirm.

This gives me an answer that is definitely not correct - 2480 when it should be around 150approx.

Regardless, I found a workaround. I had a column elsewhere that I just altered the formula to show a specific value if the text matched U8. It works now and happy with it. I can count them easy

 

[hmltnangel]

The COUNTIFS 255 characters will be the issue. I kind of thought that sadly.