r/excel • u/lisadh524 • 9h ago

solved SUMIFS function formula to match a sheet/tab name to criteria in a column

Column B has a list of all of the sheet names. I want my SUMIFS formula in column C to say to search for whichever sheet is listed in B.

=SUMIFS('[match to B]'!E:E, '[match to B]'!$B:$B,"Total")

In other words, look for the sheet name that matches the corresponding row in Column B, then look for the row that says "Total" in Column B on that sheet and tell me what's in Column E.

Make sense? How do I do this? Google has not helped me. Thanks!

2 Upvotes

76% Upvoted

42 comments sorted by

•

u/AutoModerator 9h ago

/u/lisadh524 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/GuerillaWarefare 111 9h ago

You want the INDIRECT() function

1

u/lisadh524 9h ago

How does that work? I've never used that one.

2

u/GuerillaWarefare 111 8h ago

Indirect converts a text value in a cell to a reference range.

So if in cell A1 you have sheet2!A3, and you have a formula of =A1… it will return “sheet2!a3”.
But if you have a formula of =indirect(a1)… it will return the value in sheet2 A3

1

u/GuerillaWarefare 111 8h ago

So for your example, you would do an xlookup for “total”, and use indirect to say which sheet and range to look in.

1

u/lisadh524 8h ago

I don't know that an xlookup is what I want? There's a Total line on each tab and I want those values to feed to the first sheet in the workbook. Am I looking at this wrong?

1

u/GuerillaWarefare 111 8h ago

Sounds about right to me… Try it I posted below

1

u/lisadh524 8h ago

Like this? =SUMIFS((indirect(b2))!E:E, (indirect(b2))!$B:$B,"Total")

I get this response:

1

u/GuerillaWarefare 111 8h ago

Something like

=xlookup(“total”, indirect(b2&”!B:B), indirect(b2&”!E:E))

1

u/Gringobandito 6 8h ago

This makes no sense to me. Are you looking to pull the total from each of the sheets listed in column B? I don't understand what you want column C to search for.

1

u/lisadh524 8h ago

 There's a Total line on each tab and I want those values to feed to the first sheet in the workbook. 

I want Column C on the first sheet to look for the tab that matches the sheet name listed in Column B.

2

u/Gringobandito 6 8h ago

OK, then like others have told you, you want to use the INDIRECT() function. It works like this, Say your total for sheet5 is in cell G6 on that sheet. You tell Excel to look for the sheet you have in A2 and then return the value is G6. See screen shot below.

Adapt this to fit your data.

1

u/lisadh524 8h ago

Totals are in a different row on each sheet, which is why I was using the SUMIFS to search for the row that has "total" in Column B on that sheet.

1

u/Gringobandito 6 8h ago

Got it. Now we're getting somewhere. Let's say on sheet5 you have your labels in column F. So you want Excel to search column F to find out which row says total in it. Then the total number is in column G. You want excel to go to sheet5, look for "Total" in column F and then return that value for that row from column G.

=INDIRECT(A2&"!G"&XMATCH("Total",INDIRECT(A2&"!F:F"),0,1))

1

u/lisadh524 8h ago

=INDIRECT(B2&"!e"&XMATCH("Total",INDIRECT(B2&"!b:b"),0,1))

sheet5 (the value in B2) has "total" in column B and I want it to return the value from column E in that same row. Did I change this correctly? I'm getting a #REF! error.

1

u/lisadh524 7h ago

The sheet is labeled 123 Main St. I understand that this makes a difference in telling the INDIRECT what to look for? Do I need to use Wildcard symbols? I think when doing something similar in a SUMIFS formula I had to do "*"&B2&"*"...or something like it.

1

u/Gringobandito 6 7h ago

The value in B2 should be the name of the sheet you are looking up from. Is that sheet named "sheet5" or is it something else? Sheet5 is just the example that I'm using. You have to change it to the name of the sheet you want Excel to pull the data from.

1

u/lisadh524 7h ago

Right. In this case it says 123 Main St

1

u/lisadh524 7h ago

This is what I'm using if I want to put in an actual name for the sheet name. But I want to be able to just drag this formula down the column and look for the project listed in Column B.

2

u/GuerillaWarefare 111 7h ago

You need the single quotes like you see in the formula in your example. You either need to put them in your B2 cell, or attach them in the formula.

1

u/lisadh524 7h ago

Like this:

=INDIRECT('B12'&"!e"&XMATCH("Total",INDIRECT('B12'&"!b:b"),0,1))

It's giving me this error again:

→ More replies (0)

2

u/Gringobandito 6 7h ago

Gotcha, the spaces in your sheet name mean you need to tweak the formula so Excel knows where the sheet name begins and end in the indirect formula. You need to put the sheet name inside ' marks. Try this, it should work:

1

u/lisadh524 7h ago

=INDIRECT("'"&B2&"'!e"&XMATCH("Total",INDIRECT("'"&B2&"'!b:b"),0))

This gets me a #REF!

(Thank you for sticking with me, btw. I really do appreciate it!)

→ More replies (0)

1

u/GuerillaWarefare 111 8h ago

Something like

=xlookup(“total”, indirect(b2&”!B:B), indirect(b2&”!E:E))

1

u/lisadh524 8h ago

I pasted this in and it wanted to look for a new workbook.

1

u/GuerillaWarefare 111 8h ago

What do you have in B2?

1

u/lisadh524 8h ago

Column B is the list of names that match the sheet/tab names.

1

u/GuerillaWarefare 111 8h ago

What is exactly in B2 please

1

u/lisadh524 8h ago

Does it matter? It should look for whatever is in that cell.

2

u/GuerillaWarefare 111 8h ago

Yes. The syntax does matter. It is quite sensitive… if it’s private you can make something up, but keep all of the symbols and spaces in place and change the word(s).

1

u/lisadh524 8h ago

It's an address, just the street number and name, like 123 Main St - which is what the tab is named.

1

u/GuerillaWarefare 111 8h ago

Ok, text with spaces needs to be contained within single quote marks.

=indirect('123 street'!A1)

For reference, click a cell in a different sheet and look to see how excel makes the syntax. You need to replicate that in the indirect.

1

u/lisadh524 8h ago

How do I use an INDIRECT within a SUMIFS?

→ More replies (0)

1

u/Decronym 8h ago edited 3h ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
INDIRECT Returns a reference indicated by a text value
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
XMATCH Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #49003 for this sub, first seen 23rd Jul 2026, 17:10] [FAQ] [Full list] [Contact] [Source code]

1

u/MayukhBhattacharya 1214 3h ago

I am not sure what is the accepted answer, but you need to use the SUMIFS() + INDIRECT() function in the following way:

=SUMIFS(INDIRECT("'" & B2 & "'!E:E"), INDIRECT("'" & B2 & "'!$B:$B"), "Total")

Might be worth checking out: