Microsoft Excel - Sum ans specific number - Asked By Dan on 15-Oct-15 01:13 PM

I am having a problem with the code counting a number when it should not.

So, here are numbers I have in C2:

4.2.3, 4.2.4, 5.1, 5.2, 5.4.1, 5.5.1, 6.1, 8.1, 8.5

E1 has this number in it:

4.1

The code below is in E2 and is returning the sum of 1.

It should be 0, but it is counting the 4.1 from 5.4.1.

How do it get it to only sum if the number is exactly 4.1 and not count the 4.1 part of the number from 5.4.1?

Here is the code:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($C$2,ROW($C$2)-MIN(ROW($C$2)),,1)),ISNUMBER(SEARCH(E$1,$C$2))+0)

Hello Dan,

I think one way would be to include a space and comma in E1 (that is make it a string) or if you want to keep the entry in E1 a simple number change your formula as follows.

=SUMPRODUCT(SUBTOTAL(103,OFFSET($C$2,ROW($C$2)-MIN(ROW($C$2)),,1)),ISNUMBER(SEARCH(" "&E$1&",",$C$2))+0)

Regards

Harry

Well, now 4.2.3 amd 8.5 is showing as a zeros, when they should be a 1?

It appears to not be seeing the first and last number?

Yep, it is not returning the first or last number. I'm not sure why or how to fix that.

Hi Dan,

The reason for that is that there is no space before the first and no comma after the last.  I'll have to ponder that a bit and a bit busy at the moment but will get back to you when/if I think of a way out.  That is , unless you modify your string to be searched to include the initiall space and the final comma.

Regards

Harry

Hello again Dan,

Brain dumb me!

=SUMPRODUCT(SUBTOTAL(103,OFFSET($C$2,ROW($C$2)-MIN(ROW($C$2)),,1)),ISNUMBER(SEARCH(" "&E$1&","," "&$C$2&","))+0)

Regards

Harry

Harry, that is it. THANK YOU so much!

Can I ask you to explain how the commas, exclamations and spaces work?

I am trying to learn and understand for future coding.

V/r

Dan

Hello Dan,

The & (ampersand) is just a 'join' ( the long word in concatenate) to add (in this case a space) to the start and (a comma) to the end of a string.  That then makes your search term unique (at least for a string with items separated by a comma and a space) but only if you add the space before and the comma after.  If, for instance, you added <comma space> or even just <comma> after the string you would be back in the same position that you were initially and vice versa.  Also, if, perversely, your codes included commas and spaces, you could be in trouble again and would need to find some other way of defining an unique value to search for.  Because of the added characters in the search term, they also need to be added to the string to be searched to catch the first and last terms.

The added characters only occur in the formula and so your original data is unaffected.

In your original manifestation your search terms were not necessarily unique as 4.1 could appear as such or as part of another code (5.4.1) and give a result when no unique exactly matching value existed in the searchable string.

Hope this helps but if you need more explanation just ask.

Harry

Thank you. I think I understand. Basically, how you type the numbers with spaces and commas will define how you must write the code. If you do not put them correctly in the code, you will not get the desired result.

Dan