Sum of cells over multiple sheets with VLOOKUP

[jason7579]

I am not sure if this is possible, I have done a bit of Googling but cannot see an answer for this specific problem.

I have an Excel Workbook with Tabs running from April to March for monthly data collection. Each sheet is exactly the same except for the month name. In the sheets I have 5 identical drop down lists in the same column (D68:D72) that allows the user to choose from 14 specific subjects. They can then enter Number of events in Column F, Attendees in Column H and New Joiners in Column J (I have had to merge cells for the layout of the sheet).

I have a sheet that collates data and gives a running total from what is entered on the monthly sheets. Is it possible for me to list the 14 subjects on this sheet (list will be from D68:D81) and add up the totals in columns F, H and J based on what was chosen and entered on the monthly sheets.

I hope that this is clear, any further explanation please let me know.

Thanks
Jason

 

[steve the fish]

Hi. This formula will add up all cell E1 ranges in the sheets between Jan and Mar inclusive. Is that what you mean?

=SUM(Jan:Mar!E1)

 

[jason7579]

Hi, thanks for the quick response.

I need a formula, like a VLOOKUP, that can check the text in cells D68:D72 across all sheets (April to March) and add up totals across all sheets that correspond to the exact word in D68:D72.

Like a tally of all people in the month that have attended a certain event. The only problem being the user chooses the eventvia a drop down box.

 

[XOR LX]

and add up totals across all sheets that correspond to the exact word in D68:D72.

In which column(s) are the totals?

You mention VLOOKUP, which of course only returns the first match in a given range. Does that mean that your search value will only ever occur once in a given sheet?

Regards

 

[jason7579]

In which column(s) are the totals?

You mention VLOOKUP, which of course only returns the first match in a given range. Does that mean that your search value will only ever occur once in a given sheet?

Regards

Unfortunately the search value could appear any number of times across the workbook - but likely only once per sheet.

The total number of events is in column F; attendees in column H; new joiners in column J. On the running total sheet I would like a total of all (for example) Reading Event events, attendees and new joiners across the year.

Thanks

 

[XOR LX]

First go to Name Manager (Formulas tab) and define:

Name: Sheets
Refers to: ={"Jan","Feb","Mar","Apr"}

(Or whatever happen to be the sheet names in question.)

Then, with your first subject in D68 of your Master sheet, enter this in E68:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!D:D"),$D68,INDIRECT("'"&Sheets&"'!F:F")))

which will give the sum from column F across all sheets where the corresponding entry in column D is equal to the entry in D68.

Copy down to give similar results for entries in D69, D70, etc.

The column being referenced in the sum_range (F:F here) can be altered to give the sum from another column as required.

Regards

 

[jason7579]

You are a genius. Thank you, this has really helped me save hours in the future.

Works perfectly.

 

[XOR LX]

No worries!

Cheers!