Sum formula

[wonderd]

Hey everyone I have a simple formula I'm having trouble getting to work.

Screenshot of the formula is here https://www.screencast.com/t/mp1lIes0hSlq

I only want to add cells with negative numbers if it has the word paypal or bkcd however its returning a 0 value

Correct output should be -4

 

[Legacy 436357]

Re: Help with sum formula

You may need to put the values and words in separate columns.

 

[wonderd]

Re: Help with sum formula

I can do that. If thats needed, what would the formula then be?

 

[Legacy 436357]

Re: Help with sum formula

It would help to use the Jeannie to attach html of desired area.

 

[jeffreybrown]

Re: Help with sum formula

Rule #13 :
We prefer that members do not cross-post questions to other forums, but when this does occur members should do the following:
- Post the details of your question on our forum. Do NOT simply post a link/re-direct to the question in another forum with no details posted here.
- Make it clear that you have cross-posted and provide links to the cross-posts.
Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters

 

[Legacy 436357]

Re: Help with sum formula

I have been completely ignored on the other site and asked politely to have the post and my account removed. I do not know what to do.

I am very sorry to offend anyone.

 

[Dave Patton]

Re: Help with sum formula

Could you provide several examples?

Are the numbers in text cells always negative?
Are all numbers in text cells to be included?
What total do you require?
Are the numbers single digits?

Excel 2010
	H
3	5
4	-2 paypal
5	-2 bkcd
6	3
7	4
9b

Cell Formulas
Range	Formula
H7	{=SUM(H3:H6)+SUM(IF(ISTEXT(H3:H6),LEFT(H3:H6,2)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

Re: Help with sum formula

After Text to Columns

Excel 2010
	A	B
28	After Text to Columns
29	5
30	-2	paypal
31	-2	bkcd
32	3
33		-4
9b

Cell Formulas
Range	Formula
B33	=SUM(SUMIF(B29:B32,{"paypal","bkcd"},A29:A32))

 

[jtakw]

Re: Help with sum formula

Who's xjohnson, same as wonderd?

What about this crosspost: https://www.excelforum.com/excel-formulas-and-functions/1249754-help-with-sum-formula.html

 

[wonderd]

Re: Help with sum formula

I am not xjohnson