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.

 

[FDibbins]

Hi, welcome to the forum

1st, when using a Null return (""), don't add a space between them. If you include a space, then the cell contains a space, and you need to remember that (you might forget, and use a formula that counts all cell with no returns.

I think you are almost there...
=ifiserror(index"SHEETNAME", MATCH(SHEETNAME,0)),0)+iferror(VLOOKUP(SHEETNAME,False)),0)

 

[Brittney1313]

Hi, welcome to the forum

1st, when using a Null return (""), don't add a space between them. If you include a space, then the cell contains a space, and you need to remember that (you might forget, and use a formula that counts all cell with no returns.

I think you are almost there...
=ifiserror(index"SHEETNAME", MATCH(SHEETNAME,0)),0)+iferror(VLOOKUP(SHEETNAME,False)),0)

That worked perfectly! I must have missed where i could just use an addition sign, thank you so much for the quick reply.

I have one more question for you - On that same sheet in that index&match function as well as another sheet i have using only an index&match formula (no other additions or vlookup) There are multiple items in a list with the same name, but it stops when it finds the first name. Is there something i can place in the formula so it uses all the lines with that name and adds their respective values, rather than just stopping at the first one it finds?

 

[FDibbins]

Sorry for the delay in replying, I was off-line

Both vlookup and INDEX/MATCH will generally only search until they find the 1st match - they then stop looking. If you are adding up values that match a certain criteria (eg name, product code etc), then probably SUMIFS() is what you want to use...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Amount[/td][td][/td][td]Name[/td][td]Amount[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

10​

[/td][td][/td][td]aa[/td][td]

120​

[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

20​

[/td][td][/td][td]bb[/td][td]

150​

[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

30​

[/td][td][/td][td]cc[/td][td]

180​

[/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td]

40​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]bb[/td][td]

50​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]cc[/td][td]

60​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]aa[/td][td]

70​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]bb[/td][td]

80​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]cc[/td][td]

90​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
E2=SUMIFS($B$2:$B$10,$A$2:$A$10,$D2)
copied down

 

[Brittney1313]

That is exactly what I needed to use, thanks so much!!

 

[Brittney1313]

Hello,

I have a new question for you - still working in the same SUMIFS formula as discussed above, I now need to add another search criteria based on text in another column. Basically I have the first column working because there is only 1 text criteria, where I cant figure out is when I need it to search for multiple text.

The formula I have is.....

SUMIFS(AE2:AE126,B2:B126,A3,G2:G126,H2,H2:H126,"*Text*")

This works fine when I just am searching for that 1 text item -

I cannot get it to work in my next column where I want it to return the value if it contains any of 3 words - I tried using {"*Text1*","*Text2*","Text3*"}) but it only returns values that contain Text 1, or whichever text is listed first, it doesn't search to see if the column lists the other texts. Any ideas?

 

[Aladin Akyurek]

Try...

=SUM(SUMIFS(AE2:AE126,B2:B126,A3,G2:G126,H2,H2:H126,{"*Text1*","*Text2*","Text3*"}))

 

[Brittney1313]

Try...

=SUM(SUMIFS(AE2:AE126,B2:B126,A3,G2:G126,H2,H2:H126,{"*Text1*","*Text2*","Text3*"}))

That works, however it seems to be doubling the number in the column I am searching for when the text column has both text values, I don't want to double the number, I just want the value returned if the column lists any or all of those values - any ideas?

 

[Aladin Akyurek]

That works, however it seems to be doubling the number in the column I am searching for when the text column has both text values, I don't want to double the number, I just want the value returned if the column lists any or all of those values - any ideas?

Control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH({"text1","text2","text3"},H2:H126))*(B2:B126=A3)*(G2:G126=H2),{1;1;1}),AE2:AE126))

 

[Brittney1313]

Control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH({"text1","text2","text3"},H2:H126))*(B2:B126=A3)*(G2:G126=H2),{1;1;1}),AE2:AE126))

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" ?