Need to count unique items based on multiple criteria

[NS4Excel]

I need a formula that will count the unique values in col G if col A and H match.
Example:
Col A is the ID
Col G is the Document
Col H is the timing (month end date)

I need a formula in cell S2 that will tell me how many unique Documents in Col G belong to the ID in cell A2 for the timing in cell H2.

I tried =SUMPRODUCT(--($A$2:$A$1713=A2),--($G$2:$G$1713=G2),--($H$2:$H$1713=H2)) and this was working for a bit...or so I thought. But it's not working now.

I also tried =SUM(COUNTIFS($A$2:$A$1713,A2,$H$2:$H$1713,H2)) but this just returns the number of times these appear in the list.

I attempted to attach a file but the paperclip won't open to allow me to do that. So, I've put the data below. I hope someone can help. Thanks.

ID Document Timing Formula Result desired
ID1234 15 01/31/17 4
ID5678 2 01/31/17 1
ID1234 15 01/31/17 4
ID5678 2 02/28/17 1
ID5678 99 02/28/17 2
ID1234 15 01/31/17 4
ID1234 15 02/28/17 1
ID1234 15 01/31/17 4
ID1234 16 01/31/17 1
ID5678 99 02/28/17 2

[FlameRetired]

Try this with your data.

I shifted the source data to columns A:C. Adjust according to the real data.

In D2 this formula copied down.

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E
1	ID	Document	Timing	Desired	In D2:
2	ID1234	15	1/31/2017	4	=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)
3	ID5678	2	1/31/2017	1
4	ID1234	15	1/31/2017	4
5	ID5678	2	2/28/2017	1
6	ID5678	99	2/28/2017	2
7	ID1234	15	1/31/2017	4
8	ID1234	15	2/28/2017	1
9	ID1234	15	1/31/2017	4
10	ID1234	16	1/31/2017	1
11	ID5678	99	2/28/2017	2

[NS4Excel]

Dave, this formula is taking (using your new data set) Col B into account to determine how many times the combination of A,B, and C appear. Unfortunately I need to know how many unique values are in Col B when A and C are the same.

[FlameRetired]

I don't understand.

My formula returns the desired results your initial post #1 indicates.

Am I to understand that example does not return what you want?

I attempted to attach a file but the paperclip won't open to allow me to do that.

That papeclip hasn't worked for some time.

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

[FlameRetired]

Interpreting differently array enter this formula in D2 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E
1	ID	Document	Timing	Desired	In D2
2	ID1234	15	1/31/2017	2	=SUM(IF(FREQUENCY(IF((A2=$A$2:$A$11)*(C2=$C$2:$C$11),$B$2:$B$11),$B$2:$B$11),1))
3	ID5678	2	1/31/2017	1
4	ID1234	15	1/31/2017	2
5	ID5678	2	2/28/2017	2
6	ID5678	99	2/28/2017	2
7	ID1234	15	1/31/2017	2
8	ID1234	15	2/28/2017	1
9	ID1234	15	1/31/2017	2
10	ID1234	16	1/31/2017	2
11	ID5678	99	2/28/2017	2

[NS4Excel]

I posted incorrect desired results. I'm so sorry. I hope the attachment shows as it has the correct desired results. Thanks.

[David A Coop]

Hi,

Obviously it is difficult to work out what you are asking from this.

(How to) Upload a Workbook directly to the Forum
[I](please*do not post pictures or links*to worksheets)[/}
Click Go Advanced (next to quick post),
Unfortunately the Paper Clip doesn't work any more.
Scroll down until you see Manage Attachments (in blue text.
Click this text then click the Choose File button and find your file.
Click Upload. You can then click on the Close this window
You should now be returned to the Advanced window.
Click "Submit Reply"
Note: Please do not attach password protected workbooks/worksheets

You don't need to provide the whole workbook.
Make sure there is just enough data to demonstrate your need.
It would help if you show a BEFORE and AFTER sheet in the workbook to demonstrate the process.
Make sure your desired results are shown, mock them up manually if necessary.*

Remember to desensitize the data.

[NS4Excel]

FlameRetiredd: This formula works. Thank you.

[NS4Excel]

When I put the formula from FlameRetired into my working file, sometimes I get a 0 as the result even though I have something in Col G. I'm not sure why this is happening. I will attach another file with more sample data to see if someone can help with this instance.

[NS4Excel]

Here is a revised file. You can see that the last 4 lines have a result of 0 when they should be 1.

[FlameRetired]

No file attached.

[NS4Excel]

Trying again...

[FlameRetired]

That is because the items in G17:G20 are text. The others are numbers.

Try array entering this formula.

Please Login or Register  to view this content.

[NS4Excel]

Thank you!!!

[FlameRetired]

You are welcome.

An after thought. This non-array SUMPRODUCT formula also works.

Please Login or Register  to view this content.

[samba_ravi]

i2=SUM((FREQUENCY(IF((A$2:A$20=A2)*(H$2:H$20=H2),MATCH(A$2:A$20&G$2:G$20&H$2:H$20,A$2:A$20&G$2:G$20&H$2:H$20,0)),ROW(H$2:H$20)-ROW(H$2)+1)>0)+0)
Try this array formula and copy towards down
or
I2=SUMPRODUCT(($A$2:$A$20=$A2)*($H$2:$H$20=$H2)/COUNTIFS($A$2:$A$20,$A$2:$A$20,$G$2:$G$20,$G$2:$G$20,$H$2:$H$20,$H$2:$H$20))
Try this formula and copy towards down

[NS4Excel]

The SUMPRODUCT formula is returning #DIV/0! result. I'd love to use a non-array formula if I can make it work.

[FlameRetired]

To which upload are you applying it? Both mine and nflsales SUMPRODUCT must go in the sheet ranged A2:A20.

In A2:A16 it will return #DIV/0! because you have now introduced blank cells to the range.

[samba_ravi]

to work sumproduct formula there should be no blank cells in select ranges means
in $A$2:$A$20,$G$2:$G$20,$H$2:$H$20 if there is a blank cell then it will give error

[NS4Excel]

So in order to use the non array formula, I can't have any blank cells? Unfortunately I have plenty of them as we're still filling in data but only when it applies. We have a complete list of ID's but not all have Documents assigned to them yet.

[FlameRetired]

Try this version of same.

Please Login or Register  to view this content.

[NS4Excel]

Thank you.

[FlameRetired]

You are welcome. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.