Countif function and frequency formula

[ganesh24x7]

Hi All,

Greetings to all

I need your help on countif and frequency formula using.

3 columns in Excel:

These are all the input data

Column 1: Client code
Column 2: Service Code
Column 3: Service value

My request is from the above data is:

It is an separate sheet or separate column:

Unique client list, need a number of services done to particular client. the condition is service value is not equal to 0.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Code
[/TD]
[TD]Service Code
[/TD]
[TD]Service Value
[/TD]
[/TR]
[TR]
[TD]DME
[/TD]
[TD]American PVT LTD
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]DME
[/TD]
[TD]American PVT LTD
[/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]DME
[/TD]
[TD]American PVT LTD
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]DME
[/TD]
[TD]LPG
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]AME
[/TD]
[TD]Infosys
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]AME
[/TD]
[TD]KEYS
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]AME
[/TD]
[TD]Infosys
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]AME
[/TD]
[TD]Infosys
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]AME
[/TD]
[TD] Metro
[/TD]
[TD]-2
[/TD]
[/TR]
[TR]
[TD]CME
[/TD]
[TD]Infonet
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]CME
[/TD]
[TD]Infonet
[/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]CME
[/TD]
[TD]Infonet
[/TD]
[TD]-5
[/TD]
[/TR]
[TR]
[TD]CME
[/TD]
[TD]Infonet
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]CME
[/TD]
[TD]Corp
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]



The results needs


AME = 4 (Infosys = 3, but 0 not countable and Keys 1, Metro 1)

DME = 3 (American PVT LTD = 3, but 0 not countable and LPG 1)

CME = 3 (Infonet = 5, but two 0 not countable, corp 1)

I request above results using excel formula from experts.

Let me know if you have any clarifications on this.

It would be great if it is solved us.

thanks

 

[Marcelo Branco]

Re: Countif function and frequency formula help

Welcome to Mr Excel

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Client Code​

[/td][td]

Service Code​

[/td][td]

Service Value​

[/td][td][/td][td]

Code​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

1​

[/td][td][/td][td]

AME​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

-1​

[/td][td][/td][td]

DME​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

0​

[/td][td][/td][td]

CME​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

DME​

[/td][td]

LPG​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

AME​

[/td][td]

KEYS​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

AME​

[/td][td]

Metro​

[/td][td]

-2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

-1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

-5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

CME​

[/td][td]

Corp​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in F2 copied down
=COUNTIFS(A$2:A$15,E2,$C$2:$C$15,"<>0")

Hope this helps

M.

 

[ganesh24x7]

Re: Countif function and frequency formula help

Thank you for your response.


It works fine.

Here i need one more condition on this result.

The service code need to unique, but count not equal to 0.

The above results DME = 2 (American PVT LTD 1, 1, 0)


I request you to help to get this answer.

Let me know if you have any clarifications.
Thanks

 

[ganesh24x7]

Re: Countif function and frequency formula help

Sorry:

We request unique count on service code.

The above results DME = 1 (American PVT LTD 1, 1, 0)

Sorry for this confusion.

Please do the needful

 

[Marcelo Branco]

Re: Countif function and frequency formula help

Sorry:

We request unique count on service code.

The above results DME = 1 (American PVT LTD 1, 1, 0)

Sorry for this confusion.

Please do the needful

Try
[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Client Code​

[/td][td]

Service Code​

[/td][td]

Service Value​

[/td][td][/td][td]

Code​

[/td][td]

Count Unique Service​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

1​

[/td][td][/td][td]

AME​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

-1​

[/td][td][/td][td]

DME​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

DME​

[/td][td]

American PVT LTD​

[/td][td]

0​

[/td][td][/td][td]

CME​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

DME​

[/td][td]

LPG​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

AME​

[/td][td]

KEYS​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

AME​

[/td][td]

Infosys​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

AME​

[/td][td]

Metro​

[/td][td]

-2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

-1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

-5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

CME​

[/td][td]

Infonet​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

CME​

[/td][td]

Corp​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$15=E2,IF(ISNA(MATCH(B$2:B$15,IF(A$2:A$15=E2,IF(C$2:C$15=0,B$2:B$15)),0)),MATCH(B$2:B$15,B$2:B$15,0))),ROW(B$2:B$15)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

M.

 

[ganesh24x7]

Re: Countif function and frequency formula help

I need to unique count for service code, the condition is <> 0

My requested formula result on this data is

AME = 3 (infosys = 1, 1 , 0) 1 + (Keys = 1) 1 + (Metro = -5) 1

DME = 2 (American PVT LTD = 1, -1, 0) 1 + (LPG = 1) 1

CME = 2 (Corp = 5) 1 + (Infonet = 0, -1, -5)

Let me know if you are not clear.

I am looking forward experts response ASAP.

Thanks

 

[ganesh24x7]

Re: Countif function and frequency formula help

One more example

If PME = 0 (Corp = 0) 0 + (Infonet = 0)

RESULT WILL BE
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]

Code
​

[/TD]
[TD]

Count Unique Service​

[/TD]
[/TR]
</tbody>[/TABLE]

PME = 0

I request you all any one to help on this

 

[Marcelo Branco]

Re: Countif function and frequency formula help

I misunderstood what you need

This?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Client Code​

[/TD]
[TD]

Service Code​

[/TD]
[TD]

Service Value​

[/TD]
[TD][/TD]
[TD]

Code​

[/TD]
[TD]

Count Unique Service​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

AME​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

-1​

[/TD]
[TD][/TD]
[TD]

DME​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

CME​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

DME​

[/TD]
[TD]

LPG​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

AME​

[/TD]
[TD]

KEYS​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

AME​

[/TD]
[TD]

Metro​

[/TD]
[TD]

-2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

-5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

CME​

[/TD]
[TD]

Corp​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in F3 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$15=E2,IF(C$2:C$15<>0,MATCH(B$2:B$15,B$2:B$15,0))),ROW(B$2:B$15)-ROW(B$2)+1),1))
Ctr+Shift+Enter

M.

 

[ganesh24x7]

Re: Countif function and frequency formula help

Thank you for your immediate response.

I need a condition if service value is 0 than not countable.


Please see below is another example and results. Please help on this .....


[TABLE="width: 418"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Client Code
[/TD]
[TD]Service Code[/TD]
[TD]Service Value[/TD]
[TD]Code
[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]AME[/TD]
[TD]Infosys[/TD]
[TD="align: right"]1[/TD]
[TD]AME[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]AME[/TD]
[TD]Keys[/TD]
[TD="align: right"]1[/TD]
[TD]CME[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AME[/TD]
[TD]Metro[/TD]
[TD="align: right"]1[/TD]
[TD]DME[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AME[/TD]
[TD]Infosys[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AME[/TD]
[TD]Infosys[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]Infonet[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]Infonet[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]Infonet[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]Infonet[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CME[/TD]
[TD]LPG[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DME[/TD]
[TD]American PVT LTD[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DME[/TD]
[TD]American PVT LTD[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DME[/TD]
[TD]American PVT LTD[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DME[/TD]
[TD]American PVT LTD[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Re: Countif function and frequency formula help

Use the formula in post 8 - it's an array formula so it must be confirmed with Ctrl+Shift+Enter simultaneously (not just Enter)

Repeating


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Client Code​

[/TD]
[TD]

Service Code​

[/TD]
[TD]

Service Value​

[/TD]
[TD]

Code​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

1​

[/TD]
[TD]

AME​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

AME​

[/TD]
[TD]

Keys​

[/TD]
[TD]

1​

[/TD]
[TD]

CME​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

AME​

[/TD]
[TD]

Metro​

[/TD]
[TD]

1​

[/TD]
[TD]

DME​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

AME​

[/TD]
[TD]

Infosys​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

CME​

[/TD]
[TD]

Infonet​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

CME​

[/TD]
[TD]

LPG​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

DME​

[/TD]
[TD]

American PVT LTD​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in E2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$15=D2,IF(C$2:C$15<>0,MATCH(B$2:B$15,B$2:B$15,0))),ROW(B$2:B$15)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

If you're not familiar with array formulas take a look at
http://www.cpearson.com/excel/ArrayFormulas.aspx

M.