Improve company productivity with a Business Account.Sign Up

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

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
Asked:
dannibach
  • 7
  • 4
  • 3
1 Solution
 
[ fanpages ]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
 
dannibachAuthor 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
 
dannibachAuthor Commented:
this works for 1 line only

=IF(LEFT(A8;1)=".";0;1)*B8
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
dannibachAuthor 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
 
[ fanpages ]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
 
dannibachAuthor Commented:
it just tells me that this is not a valid string

=SUM(I11:I78977) - SUM.IF(G11:G78977;".*";I11:I78977)
in english :)
0
 
[ fanpages ]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
 
dannibachAuthor 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
 
barry houdiniCommented:
>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
 
barry houdiniCommented:
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
 
dannibachAuthor 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
 
barry houdiniCommented:
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
 
dannibachAuthor Commented:
Arh god.... thanks champ

/Danni
0
 
[ fanpages ]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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now