Help with Sum formula

**sans** · 10-19-2011, 07:38 AM

Hello to everyone,

I hope someone can help me out. I have a row with the following numbers:

2-3-5-7-8-9-15-17-18-29-35

My total rows are 4000 and at each row the numbers are different but are always add up to 11 in total. For instance,

2-3-5-7-8-9-15-17-18-29-35
1-4-6-9-13-17-19-25-26-27-38
etc

What I'd like to do is to automatically get how many from the following numbers are in each row: 2-6-17-29-35. These numbers never change

For instance,

In 2-3-5-7-8-9-15-17-18-29-35 there are 4 numbers from 2-6-17-29-35
In 1-4-6-9-13-17-19-25-26-27-38 - 2 numbers
etc

Is this possible? If anyone can help me out it would be amazing as I will save days in trying to do it manually.

Thanks
Sans

**Andrew-R** · 10-19-2011, 07:44 AM

If you're looking at one row at a time (say the value is in A1) then formula you want is =SUM(--(NOT(ISERROR(FIND("-" & {2,6,17,29,35} & "-","-" & A1 & "-")))))

**sans** · 10-19-2011, 08:19 AM

Thank you so much for your help! You saved me so much time! What if the 11 numbers, are in different cells? For instance,

B3-C3-D3-E3-F3- G3- H3 - I3 - J3 -K3 - L3
2 - 3 -5 - 7 - 8 - 9 - 15 - 17 - 18 - 29 - 35

Again thank you,
Sans

**Andrew-R** · 10-19-2011, 08:25 AM

Assuming they're in columns A:K then =SUM(COUNTIF(A1:K1,{2,6,17,29,35}))

**sans** · 10-19-2011, 08:31 AM

Amazing! Thank you so much! Is there a book that includes all these formulas or do you write them on a case by case basis?

Sans

**Andrew-R** · 10-19-2011, 08:33 AM

It's a case-by-case basis for me, although I believe that some people who've been here longer than me do keep a list of answers to common questions.

**sans** · 10-19-2011, 09:10 AM

Again, thank you for your help.
Sans

**JBeaucaire** · 10-19-2011, 11:13 AM

Andrew is right, I know I have several reference "lists" of common Qs & As. Posing qualified questions in a forum (or just searching the forum or the keywords) is much quicker than thumbing through a book.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.