Help with automatically finding cells below a certain title

[johnstamosiscool]

Hi there,

I'm quite a noob at excel and figured this would be the best place to get help.

I have a sales report - type excel where there are several groups of tables .

Screen Shot 2018-01-09 at 11.19.21 PM.png

So i have about 500 tables like the two above.

I have 4 names, e.g john, james.

I would like to find the totals of each table's total where those relevant names are there.

e.g i need a way to "find" all "James", go 10 rows down and pull these values into a total..


Is this possible?

( In the attachment at the bottom there is a "totals" table..)

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[johnstamosiscool]

Thanks for the quick reply, I've attached a sheet of what i need (With such a small sheet it's easy to manually find john, james etc and just add the totals but the actual sheet has about 500 of these smaller tables...

[davesexcel]

I wouldn't know how to do it with just formulas, I can do something with VBA though.
Click button in attached workbook

Please Login or Register  to view this content.

[Glenn Kennedy]

Here's an array formula that works:

=SUMPRODUCT(IFERROR(($B$1:$Q$13=$A26)*$B$11:$Q$23,0))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[johnmpl]

Hi, to all!

You can use this formula:
[B26] : =SUMIF(A$1:Q$13,A26,A$11:Q$23)

And drag it down. Blessings!

[johnstamosiscool]

I wouldn't know how to do it with just formulas, I can do something with VBA though.
Click button in attached workbook

Please Login or Register  to view this content.

This seems to be exactly what i need, however i'm getting an error when trying the same code in my larger sheet.

what could be the issue/or which sections of the code should i adapt to which parameters of my document?
Screen Shot 2018-01-10 at 12.35.19 AM.png

[davesexcel]

I would assume it can't find what you are looking for, so there would be no unionRng.
I know you had supplied a sample, but it should have been similar to what your actual workbook layout is.

[johnstamosiscool]

I would assume it can't find what you are looking for, so there would be no unionRng.
I know you had supplied a sample, but it should have been similar to what your actual workbook layout is.

I'm sorry about that, would this attachment help in any way?

It is the full sheet without any data in it. (for cell range purposes?)

Imagine that each table has a name, descr, and price as in the smaller sample(s).


Let me know if this could help my case!

Thanks,

[johnmpl]

It would be good that, for mere decency, do not ignore the contributions that are provided, that makes the person who helps you lose interest, and thus get less help.

For your new file, just change the ranges, like this:
[B523] : =SUMIF(A$3:DO$511,A523,A$13:DO$521)

And drag it down. Blessings!

[johnstamosiscool]

It would be good that, for mere decency, do not ignore the contributions that are provided, that makes the person who helps you lose interest, and thus get less help.

For your new file, just change the ranges, like this:
[B523] : =SUMIF(A$3:DO$511,A523,A$13:DO$521)

And drag it down. Blessings!

hi there, terribly sorry for overlooking. i was quite interested in the macro on a personal level, apologies for disregarding your previous replies. your system seems to work!! Thank you.

[davesexcel]

Try this.

Please Login or Register  to view this content.

[davesexcel]

.......that makes the person who helps you lose interest, and thus get less help.......

It may not have been overlooked, I had acknowledged your answer, but a monitor decided to delete it.