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

 

[ganesh24x7]

Re: Countif function and frequency formula help

Great. It works find.

Thank you