count formula for unique ids that meet multiple criteria

fgbdrum

New Member
Joined
Jul 22, 2010
Messages
5
hello,

my table looks like this:
table 1.PNG


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:
table 2.PNG


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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,143
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,473
Try:

Book1
ABCDEFGHIJ
1IDOrgRecognition_Type_NameOrgPoint-based Reward CertificatePoint-based Reward NominationPoint-based Reward to My Direct Reports
2Z0011111SHS ABCPoint-based Reward CertificateSHS ABC100
3Z0022222SHS DEPoint-based Reward CertificateSHS DE111
4Z0033333SHS AMPoint-based Reward NominationSHS AM011
5Z0044444SHS SPPoint-based Reward NominationSHS MM001
6Z0044444SHS SPPoint-based Reward NominationSHS RR000
7Z0022222SHS DEPoint-based Reward NominationSHS SP010
8Z0088888SHS MMPoint-based Reward to My Direct Reports
9Z0088888SHS MMPoint-based Reward to My Direct Reports
10Z0003333SHS AMPoint-based Reward to My Direct Reports
11Z0022222SHS DEPoint-based Reward to My Direct Reports
Sheet16
Cell Formulas
RangeFormula
H2:J7H2=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

New Member
Joined
Jul 22, 2010
Messages
5
this worked beautifully. thank you, eric w. will use the xl2bb functionality in the future. thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,587
Messages
5,573,074
Members
412,502
Latest member
HMilne
Top