x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 337

# Excel sum

Hi

How do i make a sum in a spreadsheet where  sum is D17
where A2 to A16 contains tekst some with . as first letter.   <--(dot)
where b2 to B16 contains numbers

And i need to get all the numbers in sum EXCEPT the once from the A row starting with .  <--(dot)

RGS Danni
0
dannibach
• 7
• 4
• 3
1 Solution

IT Services ConsultantCommented:
Hi,

If I understand your requirement correctly, this formula should be placed in cell [D17]:

=SUMPRODUCT((LEFT(A2:A16,1)<>".")*(B2:B16))

BFN,

fp.
0

Author Commented:
Hey FP

thx for the fast reply... cant seem to get it to work

this is what i tryed my self.

=IF(LEFT(A2:A14;1)=".";0;1)*B2:B14

RGS Danni
0

Author Commented:
this works for 1 line only

=IF(LEFT(A8;1)=".";0;1)*B8
0

Author Commented:
Found somethink that works: here is the correct string i use:

=SUM(I11:I78977) - SUM.HVIS(G11:G78977;".*";I11:I78977)

but i need to also exclude all that begins with "test" aswell

/Danni
0

IT Services ConsultantCommented:
=SUMPRODUCT((LEFT(A2:A16,1)<>".")*(B2:B16))
... cant seem to get it to work

What do you see that is unexpected, or incorrect?
0

Author Commented:
it just tells me that this is not a valid string

=SUM(I11:I78977) - SUM.IF(G11:G78977;".*";I11:I78977)
in english :)
0

IT Services ConsultantCommented:
Why are you not using the formula I provided (based on your question text)?

=SUMPRODUCT((LEFT(A2:A16,1)<>".")*(B2:B16))
0

Author Commented:
Hi FP

i would like to use it. But i am testing my self aswell, so this is just what i found:
=SUM(I11:I78977) - SUM.if(G11:G78977;".*";I11:I78977)

i would like to use the the one you added, but as i wrote i cant seem to get it to work.
Excel tells me its  not working.

/danni
0

Commented:
>but i need to also exclude all that begins with "test" aswell

Try using SUMIFS like this

=SUMIFS(I:I;G:G;"<>.*";G:G;"<>test*")

regards, barry
0

Commented:
Given the formulas you have posted where you are using ; as a separator you probably need this version to make fp's original formula work

=SUMPRODUCT((LEFT(A2:A16;1)<>".")*(B2:B16))

...although SUMIF is possibly simpler

=SUMIF(A2:A16;"<>.*";B2:B16)

regards, barry
0

Author Commented:
Hey Barry

That works great.  Can you tell me why this gives me to different results

=SUM(I11:I78977) - SUM.HVIS(F11:F78977;".*";I11:I78977) - SUM.HVIS(F11:F78977;".*";I11:I78977)

=SUM.HVISER(I11:I78977;F11:F78977;"<>.*";F11:F78977;"<>test*")

Cheers Danni
0

Commented:
Your two SUMIF functions are both excluding ".", one of those should be test

=SUM(I11:I78977) - SUM.HVIS(F11:F78977;".*";I11:I78977) - SUM.HVIS(F11:F78977;"test*";I11:I78977)

regards, barry
0

Author Commented:
Arh god.... thanks champ

/Danni
0

IT Services ConsultantCommented:
(Sigh)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.