Assign Yes/No to Cell Based on Meeting Some Criteria

[chicity26]

Hi,

I need to write an excel function/macro/code to assign yes or no to a cell if the Description fits a certain category. I have attached a sample workbook and will explain what I mean.

In the sample workbook, there is data on a company. This included a unique ID, city, state, and description. What I want to do is fill out the yes/no column. So if a particular company has 2 of the 4 necessary "Descriptions", then column G will return yes, otherwise No. It must return yes for a particular company/ID if 2 of the "Descriptions" are "Sale", "Service", "Business", or "Par". I only want it to count at least two unique descriptions.

For example, in the sample workbook, Company A has all 4 of the necessary descriptions, thus everything in column G for this specific client should be yes. For Company B, only 1 of the 4 descriptions matches, so it returns No for all Company 2 columns. Company C has 2 of 4, so it returns yes for these columns. Note, it doesn't matter for Company C that "Business" was listed twice. It only needs to count the first unique time that description occurs.

Any suggestions on how to approach this?

Thanks.

[Bernie Deitrick]

In G2, enter the formula

=IF((COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Sale")>0)*1+(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Service")>0)*1+(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Business")>0)*1+(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Par")>0)*1>=2,"Yes","No")

I wrote this such that if company A has two branches, in different city and/or state that those will not be used. If that is not the case, COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F can be shortened to COUNTIFS(B:B,B2,F:F in all four instances

[Richard Buttrey]

Hi,

In G2 copied down

Please Login or Register  to view this content.

[Bernie Deitrick]

Richard,

That will not meet the requirement: "only needs to count the first unique time that description occurs", and it is also possible that there are other descriptions beyond the five examples that should also be ignored.

[oeldere]

with an pivot table ?

see the attached file.

[chicity26]

Thanks, the formula worked well. I have a secondary question. What if I wanted to change it such that, one of the 4 was absolutely required while the last one could come from the other three. So for example, let's say it should count yes only if it has some combination of 2 of the 4 items needed, but one of the two items needed has to be "Business". How could I adjust that?

[Bernie Deitrick]

Just use the Business part as the first conditional:

=IF(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Business")>0, IF((COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Service")>0)*1+(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Sale")>0)*1+(COUNTIFS(B:B,B2,C:C,C2,D:D,D2,F:F,"Par")>0)*1>=1,"Yes","No"),"No")