Excel 2007 : Need to sum if the numbers are multiples of 2 or 3

[sushil10s]

List of numbers, e.g,

1 2 3 4 5 6 7 8 9 10 11 12 13 14 etc. in (say) Column A.

Need to sum the numbers which are multiple of 2 and 3 [Catch is that the number multiple of both 2 and 3 need to be picked up only once, for example 6].

Thanks

[Andrew-R]

This seems to work:

=SUMPRODUCT(A1:A14,--((A1:A14/2=ROUND(A1:A14/2,0))+(A1:A14/3=ROUND(A1:A14/3,0))>0))

[shg]

Another way:

=SUMPRODUCT(A1:A14*SIGN((MOD(A1:A14,2)=0)+(MOD(A1:A14,3)=0)))

[MrShorty]

thread title says multiples of 2 or 3, post body says multiples of 2 and 3. Responses seem to assume 2 or 3. Just want to be clear, we are looking for the sum of multiples of 2 or 3?

A more "mathematically elegant" approach might be:

the sum of the multiples of 2 is simply the sum of the first n (n=7 for the sample set given) even numbers, which is fairly well known to be n(n+1). this sum will also include the even multiples of 3. the remaining part of the problem is to sum the first n odd multiples of 3, which is the same as 3*sum(1,3,5,...). the sum of the first n odd integers is n^2. At this point, we just need an algorithm to determine n for each sum. For the sum of the even integers, n=int(max(range)). for the sum of the odd multiples of 3, 2n-1=max(range)/3 (solve for n). then sum those two sums.

[Andrew-R]

thread title says multiples of 2 or 3, post body says multiples of 2 and 3. Responses seem to assume 2 or 3. Just want to be clear, we are looking for the sum of multiples of 2 or 3?

No assumption - from the OP's post, "Catch is that the number multiple of both 2 and 3 need to be picked up only once, for example 6", i.e. the sum of 1-6 is 2+3+4+6, not 2+3+4+6+6

the sum of the multiples of 2 is simply the sum of the first n (n=7 for the sample set given) even numbers, which is fairly well known to be n(n+1). this sum will also include the even multiples of 3. the remaining part of the problem is to sum the first n odd multiples of 3, which is the same as 3*sum(1,3,5,...). the sum of the first n odd integers is n^2. At this point, we just need an algorithm to determine n for each sum. For the sum of the even integers, n=int(max(range)). for the sum of the odd multiples of 3, 2n-1=max(range)/3 (solve for n). then sum those two sums.

Which makes the *massive* assumption that the OP is always trying to sum a sequence of 1..n numbers. A more likely assumption is that they used the numbers 1-14 as an example only, and their actual data is nothing like as straightforward.

[daddylonglegs]

Another variation....

=SUMPRODUCT(A1:A14,(MOD(A1:A14,2)*MOD(A1:A14,3)=0)+0)

[shg]

@ MrS:

A more "mathematically elegant" approach might be ...

Your asssumption may be wrong, but it could equally be right. I like it.

@DLL: Nice