Modified COUNTIF but only counting unique values

[Badvgood]

I am looking to find a COUNTIF based formula (I think)

Column C I have a list of account codes. For the first part of my problem, I want to focus on the ones that start with a lowercase z, but not the ones that start with z88.

Against each time an account code appears in C, a WIP Number will be in column J.

A WIP number is likely to appear several times in the list and it may or may not have different account numbers next to it.

In this snapshot image example, I would expect:

WIP 10001 to be counted once
WIP 10008 to be counted once
WIP 10010 to be counted once
WIP 10011 to be counted once
WIP 10012 to be counted once

The account codes starting with z88 are ignored

Any ideas on how to solve the first part of my problem before I move onto the next steps?

Example workbook also attached if needed


Thanks, BVG

[Glenn Kennedy]

Maybe this:

=SUM(INDEX((CODE(LEFT($C$2:$C$1831,1))=122)*(LEFT($C$2:$C$1831,3)<>"z88")/COUNTIFS($C$2:$C$1831,$C$2:$C$1831&"",$J$2:$J$1831,$J$2:$J$1831&""),0))

Not checked, as the sample, at 1830 rows slightly exceeds what we like to see as a sample size.... 10-20 rows maximum!! (see yellow banner, top of page).

[Badvgood]

Maybe this:

=SUM(INDEX((CODE(LEFT($C$2:$C$1831,1))=122)*(LEFT($C$2:$C$1831,3)<>"z88")/COUNTIFS($C$2:$C$1831,$C$2:$C$1831&"",$J$2:$J$1831,$J$2:$J$1831&""),0))

Not checked, as the sample, at 1830 rows slightly exceeds what we like to see as a sample size.... 10-20 rows maximum!! (see yellow banner, top of page).

Good evening Glenn,

Thank you for taking a look at this for me, very much appreciated.

Three things if you have a moment?

1) Apologies for the larger than normal sample data, point noted.

2) In your formula, the first part that states =122 What is that part of the formula doing

3) I'm not sure your formula works or my methodology for checking it is wrong, which of course is entirely possible! In my very layman's testing method, I filtered the account column by z2208, copied the WIP numbers to a fresh column, then removed the duplicates. This gave me 518 unique WIP Numbers.

Your thoughts, please?

Kind regards BVG

[Glenn Kennedy]

122 is the code for a lower case Z.

Did you filter by z22 as stated immediately above or by z88, as stated in post 1?

I'm away for the night.

[Badvgood]

122 is the code for a lower case Z.

Did you filter by z22 as stated immediately above or by z88, as stated in post 1?

I'm away for the night.

Thanks Glenn,

I understand on the 122 now - Never thought you could do that so I have picked up a very useful tip. Thank you.

Spotted the z22 part, there were a few others in there that makes it all tally, your formula is spot on.

[Glenn Kennedy]

In response to your PM. Set up two named ranges. this will autoadjust to the length of your data. CTRL-F3 to view/edit. There are two forms here, one for account(Text) and one for WIP (a number)

For account

='GSC Service Composite Tech Hrs'!$C$2:INDEX('GSC Service Composite Tech Hrs'!$C:$C,MATCH("Zzzz",'GSC Service Composite Tech Hrs'!$C:$C))

and for WIP:

='GSC Service Composite Tech Hrs'!$J$2:INDEX('GSC Service Composite Tech Hrs'!$J:$J,MATCH(1E+100,'GSC Service Composite Tech Hrs'!$J:$J))

the formula then becomes:

=SUM(INDEX((CODE(LEFT(Account,1))=122)*(LEFT(Account,3)<>"z88")/COUNTIFS(Account,Account&"",WIP,WIP&""),0))

which has the added advantage of being more human-readable!!