How to calculate how many items are associated with an ID

Question

How to calculate how many items are associated with an ID

Navigation

#1 by (-1 votes)

1

I'm working on excel and in one of the formulas I have to do, I calculate how many different items are associated with a specific ID. I have the following columns

      ID               Item 
OPX-0020433077  Continuity Services
OPX-0020433077  Storage Services
OPX-0020433077  Midrange Services
OPX-0020433077  Storage Services
OPX-0020433079  Digitization
OPX-0020433080  CPS-Other 3rd Party Products and Services
OPX-0020433080  CPS-Other 3rd Party Products and Services
OPX-0020433080  CPS-Other 3rd Party Products and Services
OPX-0020433081  Foundational Support Services
OPX-0020433081  Storage Services
OPX-0020433081  Foundational Support Services
OPX-0020433081  Storage Services
OPX-0020433081  Midrange Services

Therefore the result I should get is something like the following.

     ID            Count
OPX-0020433077       3  
OPX-0020433079       1
OPX-0020433080       1
OPX-0020433081       3

This is because the id OPX-0020433077 has 4 items, but there is one of them that is repeated, therefore it would be counted as if they were 3, since the repeated one is ignored.

I have been using the following code.

=COUNT(IF($A$2:$A$11=A2,$B$2:$B$11))

But this formula always returns me 0. Some idea of how I could do it, can be using VBA or excel functions directly. Thank you.

condiciones vba excel formulas

asked by Carlos Arronte Bello 04.12.2018 в 15:21

source

1 answer

Select table in PHP by variable I receive this error when compiling a project: error: can not find symbol method setLatestEventInfo

score -1 · Answer 1

I think it would be best to remove duplicates and then summarize with a dynamic table, but just in case, without using VBA, I can think of the following:

Use an auxiliary column, in which you return 1 if it is the first time that ID associated with that ITEM appears, and return 0 if it is not the case.

For this account, I used the following formula:

=SI(CONTAR.SI.CONJUNTO($A$2:A2;A2;$B$2:B2;B2)=1;1;0)

We conjugate it with dynamic ranges (we only leave the first coordinate as an absolute reference) so that the account is updated alone, as the range is extended. In this way, the first time it appears, for example, OPX-0020433077 Storage Services the formula returns a 1, but the successive times it appears, it will return 0.

Then, with a dynamic table we summarize, and add that column:

Thus we obtain, for example, that the ID OPX-0020433077 has 3 items in total.

I hope you can adapt this to your needs.