Counting and Summation Question

[mangoMan1]

Here is the data I am dealing with. Two columns ID and Type:

ID Type
1 Report
1 Report
1 Workflow
2 Form
3 Report
3 Form

I need to summarize this as:

Report 2
Workflow 1
Form 2

I need to only count the same Type with the same ID once. Currently I am using COUNTIF but that counts report with id 1 twice. Also wanted to add that ID can be alpha neumeric

Thanks,

[oeldere]

You get better help if you follow the advice in the link below.

http://www.excelforum.com/the-water-...-question.html

[AlKey]

Please see attached file.

[Marcol]

One way might be to use a hepler column, this can be hidden
In C2 Drag/Fill Down

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($B$2:$B$100&" "&$A$2:$A$100,MATCH(TRUE,INDEX(ISNA(MATCH($B$2:$B$100& " "&$A$2:$A$100,$C$1:$C1,0)),0),0))))

Then in E2 Drag/Fill Down

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($B$2:$B$100,MATCH(TRUE,INDEX(ISNA(MATCH($B$2:$B$100,$E$1:$E1,0)),0),0))))

And in F2 Drag/Fill Down

=IF(E2="","",SUMPRODUCT(--ISNUMBER(SEARCH(E2,$C$2:$C$100))))

[mangoMan1]

Thanks! Will this work with alpha numeric or plain text IDs?

[Marcol]

It should do, try it and see if it does as you expect it to.