count formula for unique ids that meet multiple criteria

[fgbdrum]

hello,

my table looks like this:

i would like to use formulas to count the number of unique ids, based on org and recognition type. end result would look like this:

notice that id Z0044444, in org SHS SP, is only counted once in the point based reward column, even though this id received two separate awards. i only want it counted once. same with id Z0088888, in org SHS MM, in the point based reward to my direct reports column.

i've tried COUNTIFS combined with a SUMPRODUCT but that doesn't seem to be doing the trick. happy to post a sample workbook if that makes it easier.

thank you in advance for any tips.

 

[steve the fish]

Hi. It would be more useful and you would definitely get more/better responses if you didnt put images of your data in your post but tables that we can copy and paste into excel and test.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	ID	Org	Recognition_Type_Name				Org	Point-based Reward Certificate	Point-based Reward Nomination	Point-based Reward to My Direct Reports
2	Z0011111	SHS ABC	Point-based Reward Certificate				SHS ABC	1	0	0
3	Z0022222	SHS DE	Point-based Reward Certificate				SHS DE	1	1	1
4	Z0033333	SHS AM	Point-based Reward Nomination				SHS AM	0	1	1
5	Z0044444	SHS SP	Point-based Reward Nomination				SHS MM	0	0	1
6	Z0044444	SHS SP	Point-based Reward Nomination				SHS RR	0	0	0
7	Z0022222	SHS DE	Point-based Reward Nomination				SHS SP	0	1	0
8	Z0088888	SHS MM	Point-based Reward to My Direct Reports
9	Z0088888	SHS MM	Point-based Reward to My Direct Reports
10	Z0003333	SHS AM	Point-based Reward to My Direct Reports
11	Z0022222	SHS DE	Point-based Reward to My Direct Reports
Sheet16

Cell Formulas
Range	Formula
H2:J7	H2	=SUM(SIGN(FREQUENCY(IF($B$2:$B$11=$G2,IF($C$2:$C$11=H$1,MATCH($A$2:$A$11,$A$2:$A$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

And to Steve the Fish's point, check out the XL2BB button in the response box, which is what I just used.

 

[fgbdrum]

this worked beautifully. thank you, eric w. will use the xl2bb functionality in the future. thanks again.

 

[Eric W]

Glad we could help! ?