update "CountIF" formula

Hello,

I would like to update the following formula below, so it takes into account "column C and B" to get the unique count.

=COUNTIFS(Sheet1!$E:$E,"*"&Sheet4!E5&"*",Sheet1!$D:$D,"*"&Sheet4!$B6&"*")

I have attached sample workbook, with sheet1 showing input data and sheet4 showing current and desired output.

testing_EU.xlsm


Any help would be very much appreciated.

Thank you.

Thank you so much for your response.
I really appreciate your your time and help, but I am sorry to inform, I am little confused regarding your solution.

I tested out the formula on new data and its not picking up correct count for one of the values, which i have highlighted in the attachment below in sheet4.

https://www.box.com/s/tyk3nwozm1hrx1wr7tpz

any help regarding that, would be very helpful.

thank you.

Thank you so much for response again, I am sorry to inform, your post does not have any attached workbook.

thank you for your time and help.

Thank you so much for your response and time with this thread. I really appreciate your time and help.

In your previous Workbook you show your Desired Output as Copied on the Left. From that I assumed that you wanted to count "RBS" and if the count of any other Letters were > 1, you wanted the result to be 1

I apologies for the unclear information. The output is actually based on sheet1 input table, which counts how many time one particular name has one certain type, for example: there are 5 or rbs names with type abs and so on.

My original formula below, counts every name and its type. however, i would like to update the formula, by adding a condition such as:
if two or more rows from columns b and c are not duplicate then only extract one count for the name's type, otherwise if they are duplicate then extract all the count for the name's type.

I would like to ask, if this task is possible to embed in a formula or would i need to create macro.

I really appreciate your time and help with this problem.
Many thanks