Data matching formula

[mahershams]

Hi all

I want to check whether Cell 2 company or Cell D2 company code purchase both Public and Priv. in the full database. Say for example, if Global Business Solutions Ltd purchase both public and priv. any time in the full data set, it may indicate as "both purchasing" and otherwise nothing.

How to write this formula which count only if a company have purchased both public and priv. in the dataset. Please see the attached excel sheet.

Thanks
Shams

[Ace_XL]

In F2

=IF(AND(COUNTIFS($D$2:$D$1312,D2,$E$2:$E$1312,"Priv."),COUNTIFS($D$2:$D$1312,D2,$E$2:$E$1312,"Public")),"Both purchasing","")

Copy down

[Fotis1991]

Do you mean?

=and(COUNTIFS($C$2:$C$1041,C2,$E$2:$E$1041,"Priv.")>0,COUNTIFS($C$2:$C$1041,C2,$E$2:$E$1041,"Public")>0)

[mahershams]

Dear all

Thank you very much for quick solution. It works very well.

Great!!!

Regards
Shams

In F2

=IF(AND(COUNTIFS($D$2:$D$1312,D2,$E$2:$E$1312,"Priv."),COUNTIFS($D$2:$D$1312,D2,$E$2:$E$1312,"Public")),"Both purchasing","")

Copy down