Count specific numbers within a group of numbers

**ickle1999** · 04-06-2021, 05:40 AM

Hi,

I have a spreadsheet with around 10,000 rows capturing information on our suppliers.

This contains a group of categories which identifies the type of work that the supplier does for us.

I have been asked to identify suppliers that do specific types of work and to provide an overall count of the number of suppliers for each of the criteria.

The data looks like (example spreadsheet attached):

Supplier Number Categories

Supplier C 345678 1,3,4,5,6,9,14,17
Supplier D 456789 1,3,4,5,6,7,8,9,13,14,15,17
Supplier E 567890 1,3,4,5,6,7,9,11,13,14,15,17,18
Supplier F 678901 1,2

I have been asked to find out information such as:

Both 15 & 18
17 but 15 & 18 Don’t exist
16 but 15,17 & 18 Don’t exist
14 but 15,16,17 & 18 Don’t exist

If i could understand how to make up the basic formula i could use this code and manipulate it to meet the other examples that are required.

Can anyone provide some guidance on how this can be done please?

Any help appreciated

Thanks
Ickle

**AliGW** · 04-06-2021, 05:56 AM

Cumbersome possibilities:

=IFERROR(IF(IF(FIND("15",C2),1)+IF(FIND("18",C2),1)=2,"Yes"),"")

=IF(AND(IFERROR(FIND("15",C2),"No")&IFERROR(FIND("18",C2),"No")="NoNo",IFERROR(FIND("17",C2),"No")<>"No"),"Yes","")

=IF(AND(IFERROR(FIND("15",C2),"No")&IFERROR(FIND("17",C2),"No")&IFERROR(FIND("18",C2),"No")="NoNoNo",IFERROR(FIND("16",C2),"No")<>"No"),"Yes","")

=IF(AND(IFERROR(FIND("15",C2),"No")&IFERROR(FIND("16",C2),"No")&IFERROR(FIND("17",C2),"No")&IFERROR(FIND("18",C2),"No")="NoNoNoNo",IFERROR(FIND("1",C2),"No")<>"No"),"Yes","")

**ickle1999** · 04-06-2021, 11:05 AM

Brilliant - Thanks Ali

Steve C

**AliGW** · 04-06-2021, 11:06 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Count specific numbers within a group of numbers

LinkBack

Thread Tools

Rate This Thread

Display

Count specific numbers within a group of numbers

Re: Count specific numbers within a group of numbers

Re: Count specific numbers within a group of numbers

Re: Count specific numbers within a group of numbers

Thread Information

Users Browsing this Thread

Similar Threads

a formula to evaluates how many numbers in a group of 5 numbers are part of a lot of nbrs

Highlight numbers in a column +/- 2.5 from a select group of numbers

[SOLVED] Function possible for Using specific number to count other specific numbers total ?

[SOLVED] Search Group of Numbers in Cel Rangel and Highligh matched numbers.

Program to randomly selecting numbers from a group of numbers

[SOLVED] Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up?

[SOLVED] count a group of numbers but do not count duplicates

Bookmarks

Bookmarks

Posting Permissions