Count of row groupings

[mattm8]

G'day forum,

was hoping someone can help me with with this problem.

My sheet contains 2 columns, id and document:
ID Document
1 doctype1
1 doctype2
2 doctype1
2 doctype2
3 doctype3
4 doctype3
5 doctype1
5 doctype2
How can i count the number of distinct IDs that have a particular set of doctypes?
e.g., 3 IDs have doctype 1 & 2 and 2 IDs have doctype3.
thanks

[Sam Capricci]

if I'm understanding your issue it seems a countif formula would work where you might set up a distinct list of the doctypes then do a countif of those types. Am I missing something?

EDIT: So I put your distinct list of doc types in col D then in col E =countif(B:B,E1) seemed to work.
EDIT2, put your list as typed in col A and col B then made the distinct list in col D etc as noted above.

[mattm8]

Thanks Sambo kid.
that gives a count of how may id have doctype1 or doctype2 etc.
What i need is something at will show how many ID have both doctype1 and doctype2

[Sam Capricci]

So you want doctype1 and doctype2 to be combined but doctype3 to be counted separately, right?

[mattm8]

yep, thats right

[Sam Capricci]

Sorry for delaying my response, working from home on my iMac and excel for apple is a nightmare, not as many functions. Anyway, thinking about it if I needed to do it I would probably set up a helper column so in say col C, put in an if then stmt like
=if(B2="doctype3","C","A&B") then do a countif stmt in col D of the A&B and C in column C.
Tried it and it worked. It may not be the most elegant way and there may be a way using index or sumproduct to do it but offhand I can't think of another way.

[mattm8]

that's great, i appreciate your help!

[Sam Capricci]

Sure, and thanks for the reputation points.