Sum Formula Nesting with Multiple Functions

[Brittney1313]

Hello,

I am trying to figure out how sum up two different functions into one cell. I have figured out the initial formula to sum the functions, I am using an array formula with the sum nested, however, I cannot figure out how to get just the one functions answer to populate if the second function does not exist for that particular column.

The two functions I am using are 1) Index&Match to populate one set of numbers that exist on another sheet based on a name, and 2) the vlookup function to lookup another set of numbers that exist on another separate sheet based on a name. The function I have sums both of these numbers but it only works when both names exist on both separate sheets. I would like to implement a formula that adds the two when they both exist, and also only lists the one item that exists if the second does not (the index&match has a number to populate, but the vlookup does not have that name on that sheet to populate a number)

Should i be using something other than Sum? I could not figure out if i am able to use a SumIF in this situation, as sometimes there are numbers in both sets of functions, and sometimes only 1 set of the functions has a number, and sometimes none of them will populate a number as the name is not listed in either separate sheet.

Basically the formula is something like this =iferror(sum(iferror(index"SHEETNAME", MATCH(SHEETNAME,0))," "), VLOOKUP(SHEETNAME,False))," ")

Again like i said it only works if both the indexmatch and vlookup exist, it will not populate just one of the values if there is one only.

Any help would be greatly appreciated. Thank so much.

 

[Aladin Akyurek]

That worked perfectly - One question though: I have two columns, the first is calculating the information just based on one text field, the second is using the formula you listed based on two other text fields - sometimes the column these are pulling from lists both the text from the 1st column and text from the 2nd column, I only want the second column to pull the information if it does not have the first column text already, can I somehow add that part into the formula? to have it all add if it contains the first two texts listed but not "text" ?

Try to post a small sample along with the desired results, as I have done in order to set up the formula for collision-free summing:

Row\Col	A​	B​	G​	H​	AE​	AF​	AG​
2​		x	text1	text1	5​		35​
3​	x	x	text1	text2 and text1	10​
4​		x	text1	text3 or text1	20​

 

[Brittney1313]

Try to post a small sample along with the desired results, as I have done in order to set up the formula for collision-free summing:

Row\Col	A​	B​	G​	H​	AE​	AF​	AG​
2​		x	text1	text1	5​		35​
3​	x	x	text1	text2 and text1	10​
4​		x	text1	text3 or text1	20​

Okay - the information is on two different sheets - but here is basically what I am looking for


Name Description Category Amount
a sentence with text1 z 5
b Sentence with Text 2 x 10
c Sentence with Text 1 and 2 z 4
d Sentence with Text 3 z 2
e Sentence with Text 1 and 3 z 6



Column 1
list "Amount" if Name is listed as "Category" "Z" and "Description" mentions only the word Text 1, and not Text 2 or 3

Column 2
list "Amount" if Name is listed as "Category" "Z" and "Description" mentions the word Text 2 or Text 3

 

[Brittney1313]

I need the SUMIF though because there are multiple listings with the same name as well

 

[Aladin Akyurek]

Okay - the information is on two different sheets - but here is basically what I am looking for


Name Description Category Amount
a sentence with text1 z 5
b Sentence with Text 2 x 10
c Sentence with Text 1 and 2 z 4
d Sentence with Text 3 z 2
e Sentence with Text 1 and 3 z 6



Column 1
list "Amount" if Name is listed as "Category" "Z" and "Description" mentions only the word Text 1, and not Text 2 or 3

Column 2
list "Amount" if Name is listed as "Category" "Z" and "Description" mentions the word Text 2 or Text 3

I need the SUMIF though because there are multiple listings with the same name as well

Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.