Multi tab search

[tezza]

Hi all

What's the best way to do the following:

I have a summary tab listing staff names in Col A which needs the total number of hours from four other tabs (if the name exists) in Col C.

Check if the name exists in 4 other tabs under Col A (tab names change each month), if it does then sum the hours from Col C

My formula is getting long and throws up an error if the name doesn't exist.

 

[etaf]

you could use a (countif(Tab1,name) + countif(Tab2,name) + countif(Tab3,name) + countif(Tab4,name))=4
to test the names are in ALL tabes
I'm assuming the names can be in any order on the sheets
then if you have 4 - you can do a SUMIF(tab1 , name) + SUMIF(tab2 , name) + SUMIF(tab3 , name) + SUMIF(tab4 , name)
in an IF ()

=IF ( (countif(Tab1,name) + countif(Tab2,name) + countif(Tab3,name) + countif(Tab4,name))=4 , SUMIF(tab1 , name) + SUMIF(tab2 , name) + SUMIF(tab3 , name) + SUMIF(tab4 , name) , 0)

(tab names change each month)

What do the tab names change to

you maybe able to use an indirect()
and if the tabs are all together
sheetname1:sheetname4

BUT

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[tezza]

you could use a (countif(Tab1,name) + countif(Tab2,name) + countif(Tab3,name) + countif(Tab4,name))=4
to test the names are in ALL tabes
I'm assuming the names can be in any order on the sheets
then if you have 4 - you can do a SUMIF(tab1 , name) + SUMIF(tab2 , name) + SUMIF(tab3 , name) + SUMIF(tab4 , name)
in an IF ()

=IF ( (countif(Tab1,name) + countif(Tab2,name) + countif(Tab3,name) + countif(Tab4,name))=4 , SUMIF(tab1 , name) + SUMIF(tab2 , name) + SUMIF(tab3 , name) + SUMIF(tab4 , name) , 0)

Hi

Would that then sum if the name only exists 1 - 3 times? As staff come and go so it's not always 4.

 

[tezza]

My initial formula went along the lines of using iferror with vlookup for each tab. I just wondered if there was a shorter way.

 

[etaf]

so the names do not have to be ON every TAB , which is what I assumed

how about
SUMIF(tab1 , name cell) + SUMIF(tab2 , name cell) + SUMIF(tab3 , name cell) + SUMIF(tab4 , name cell)

then if not in the tab , just returns a zero

But if the tab names change each month - then that mayneed an indirect()

But you can use Tab1:Tab4, actually not sure in sUMIF() works with SUM() - but sumproduct will with a range of sheet names

Sum across multiple worksheets with criteria

To conditionally sum identical ranges in separate worksheets, you can use a formula based on the SUMIF function, the INDIRECT function, and the SUMPRODUCT function. In the example shown, the formula in F5 is...

exceljet.net

Sum if Across Multiple Sheets – Excel & Google Sheets - Automate Excel

This tutorial will demonstrate how to use the SUMPRODUCT and SUMIFS Functions to sum data that meets certain criteria across multiple sheets in Excel and Google Sheets. Regular Sum Across Multiple Sheets Sometimes your data might span several worksheets in an Excel file. This is common for data...

www.automateexcel.com

 

[tezza]

so the names do not have to be ON every TAB , which is what I assumed

how about
SUMIF(tab1 , name cell) + SUMIF(tab2 , name cell) + SUMIF(tab3 , name cell) + SUMIF(tab4 , name cell)

then if not in the tab , just returns a zero

But if the tab names change each month - then that mayneed an indirect()

But you can use Tab1:Tab4, actually not sure in sUMIF() works with SUM() - but sumproduct will with a range of sheet names

Sum across multiple worksheets with criteria

To conditionally sum identical ranges in separate worksheets, you can use a formula based on the SUMIF function, the INDIRECT function, and the SUMPRODUCT function. In the example shown, the formula in F5 is...

exceljet.net

Sum if Across Multiple Sheets – Excel & Google Sheets - Automate Excel

This tutorial will demonstrate how to use the SUMPRODUCT and SUMIFS Functions to sum data that meets certain criteria across multiple sheets in Excel and Google Sheets. Regular Sum Across Multiple Sheets Sometimes your data might span several worksheets in an Excel file. This is common for data...

www.automateexcel.com

That would only work if the names are in the same position on every sheet, unfortunately this is not the case. If one leaves the the list below moves up a row so it has to search for the name initially.

 

[etaf]

same position do you mean column or can they be in any column ?

 

[tezza]

same position do you mean column or can they be in any column ?

Same, Column A

 

[etaf]

sumif() should work
wont care which row the name is in - so long as in same column as the SUMIF() for that TAB

 

[tezza]

sumif() should work
wont care which row the name is in - so long as in same column as the SUMIF() for that TAB

Thank you, yes it works like a dream