Sumproduct by ID

[jjacker]

Hi Everyone,

I'm trying to use a Sumproduct formula with no luck on getting the right value OR im not using the right function.

I need to identify the TierNumber on a seperate column based on if they have the ID or not. Here's a quick view.



So I need to fillup the TierNum column if:
Tier1 = CaseNum has TC but no CA
Tier2 = CaseNum has TC and CA
Tier3 = CaseNum has CA but no TC

Thanks in advance

 

[navic]

If I understood you well
In F2 try (copy down)

=IF(COUNTIFS($A$2:$A$8,"Case "&RIGHT($E$1,1),$B$2:$B$8,E2)>0,"TRUE","FALSE")

In H2 try (copy down)

=IF(COUNTIFS($A$2:$A$8,"Case "&RIGHT($G$1,1),$B$2:$B$8,G2)>0,"TRUE","FALSE")

In J2 try (copy down)

=IF(COUNTIFS($A$2:$A$8,"Case "&RIGHT($I$1,1),$B$2:$B$8,I2)>0,"TRUE","FALSE")

 

[navic]

After re-reading your text, I realized that I was wrong.
Try the formula in a C2 cell

=IF(AND(IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"TC")>0,TRUE,FALSE)=TRUE,IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"CA")>0,TRUE,FALSE)=FALSE)=TRUE,1,IF(AND(IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"TC")>0,TRUE,FALSE)=TRUE,IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"CA")>0,TRUE,FALSE)=TRUE)=TRUE,2,IF(AND(IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"TC")>0,TRUE,FALSE)=FALSE,IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"CA")>0,TRUE,FALSE)=TRUE)=TRUE,3,"")))

 

[shrivallabha]

You can also try:
=IF(COUNTIFS([CaseNum],[@CaseNum],[ID],"TC")=COUNTIF([CaseNum],[@CaseNum]),"Tier 1",IF(COUNTIFS([CaseNum],[@CaseNum],[ID],"CA")=COUNTIF([CaseNum],[@CaseNum]),"Tier 3","Tier 2"))

Or a bit fancy with one more extra concatenation:
="Tier "&IF(COUNTIFS([CaseNum],[@CaseNum],[ID],"TC")=COUNTIF([CaseNum],[@CaseNum]),1,IF(COUNTIFS([CaseNum],[@CaseNum],[ID],"CA")=COUNTIF([CaseNum],[@CaseNum]),3,2))

 

[Peter Bartholomew]

One dynamic array formula to complete all 12 cells:



Note: The Tier# headers are simply digits {1,2,3} but number formatting is used to show "Tier"

 

[p45cal]

For fun, but it needs to be array-entered: cell C2 then copied down:

=VLOOKUP(MAX(([CaseNum]=[@CaseNum])+([ID]="TC")) & MAX(([CaseNum]=[@CaseNum])+([ID]="CA")),{"21","Tier 1";"22","Tier 2";"12","Tier 3"},2,0)

or for completeness where neither are present:

=VLOOKUP(MAX(([CaseNum]=[@CaseNum])+([ID]="TC")) & MAX(([CaseNum]=[@CaseNum])+([ID]="CA")),{"21","Tier 1";"22","Tier 2";"12","Tier 3";"11","Tier unknown"},2,0)

[Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.]

 

[bosco_yip]

Adopt p45cal's idea, another option but without array-entry.

In C2, copied down :

=TEXT(INDEX((MAX((A$2:A$8=A2)+(B$2:B$8="TC"))&MAX((A$2:A$8=A2)+(B$2:B$8="CA")))-21,),"Ti\er2;Ti\er3;Ti\er1")

 

[jjacker]

Sorry for late reply guys, I was away from my computer on couple of days.

I'll try these formulas above, thank you so much everyone!

 

[jjacker]

@bosco_yip how did you come up with the number -21?

and, I understand that the textformat value is (+;-;0) but how to edit the text value?
let's say I need to change the values Tier2;Tier3;Tier1 to Dipsy;Lala;Po respectively?

Apologies, I tried to solve it but my logic powers are not enough to be like you thank you!

 

[bosco_yip]

@bosco_yip how did you come up with the number -21?

and, I understand that the textformat value is (+;-;0) but how to edit the text value?
let's say I need to change the values Tier2;Tier3;Tier1 to Dipsy;Lala;Po respectively?

Apologies, I tried to solve it but my logic powers are not enough to be like you thank you!

Please see the revised text format >>

 

[jjacker]

Thank @bosco_yip that works well! now I get the syntax! put a backslash on a letter that's used for value formatting.

and you did minus it to 21 because, number of rows times 3 right? thank you soo much

 

[bosco_yip]

Thank @bosco_yip that works well! now I get the syntax! put a backslash on a letter that's used for value formatting.

and you did minus it to 21 because, number of rows times 3 right? thank you soo much

Please see this part of formula (highlighted in red):

=TEXT(INDEX((MAX((A$2:A$8=A2)+(B$2:B$8="TC"))&MAX((A$2:A$8=A2)+(B$2:B$8="CA")))-21,),"Ti\er2;Ti\er3;Ti\er1")

will return 21, 22, and 12, while minus 21 become 0, 1 and -9

Since,

text format value is (Positive;Negative;Zero)

so,

the formula text format value become >> "Ti\er2;Ti\er3;Ti\er1"

Regards
Bosco

 

[p45cal]

getting silly (still array-entered):

="Tier " & CHOOSE(BIN2DEC(MAX(([CaseNum]=[@CaseNum])*([ID]="TC")) & MAX(([CaseNum]=[@CaseNum])*([ID]="CA"))),3,1,2)

(couldn't lose the CHOOSE and use the BIN2DEC value directly in this case).

 

[jjacker]

lots of option, all working perfectly, thank you ALL!