Help. Summing multiple returns from an Array Formula lookup.

[Excel Trier]

Hi,

I have the following array formula that returns a single numerical value from tables based on 3 lookup criteria. It works perfectly.

=INDEX(OFFSET($A$1,MATCH($J3,$A$1:$A$19,0),2,5,4),MATCH($J3&$K3,$A$1:$A$19&$B$1:$B$19,0)-MATCH($J3,$A$1:$A$19,0),MATCH($L3,OFFSET($A$1,MATCH($J3,$A$1:$A$19,0)-1,2,1,4),0))

But, I now have multiple tables in the same sheet, and want the array formula to return a sum of all values available with the 3 lookup criteria.
Not just the single value from the first instance of the 3 lookup criteria, as it does presently.

Is there an easy way to do so?

[I tried simply adding SUM( ) to the front of the formula, but it didn't work]

Thank you in advance.

[:) Sixthsense :)]

Please attach a sample workbook with expected output for better understanding

[Excel Trier]

Small example file.

[:) Sixthsense :)]

Please keep a constant subject headers in one place (i.e.) C2:F2 alone instead of mingled headers.

In N3 Cell
=SUMPRODUCT(($A$3:$A$37=$J3)*($B$3:$B$37=$K3)*($C$2:$F$2=$L3),$C$3:$F$37)
Drag it down...

[Excel Trier]

Thanks for the effort. But unfortunately I cannot control the style of the headers. I receive the sheet from a third person.

Is there anyway to adjust the existing array formula? It handles the varying headers perfectly, and is flexible for more complicated similar sheets I use.

rgds.

[:) Sixthsense :)]

Sorry to say that you have to restructure the data which you receive and keep it in a specific format, because going for a hectic approach is not the right way of finding the solution

[Excel Trier]

Thanks for trying Sixthsense! If I ever have a chance to adjust the format of the data I will try your formula kindly provided. This would be my preferred way.

Unfortunately I need a hectic approach to solve this one

[JBeaucaire]

Your original formula works because alone, it IS a standard format. As soon as you jumble headers and values randomly into the same columns, formulas become untenable.

At this point, it IS time to take control of the process, even if you've never done so in the past and think you can't, you have to convince yourself to try.

Create a new format that lists the Days in column A and the names in column B, but then across row 1 you put a permanent title in for Math, Gym, etc. There are 10 subjects, so list them in 10 columns. Perhaps adjust the text angle so it is vertical and takes less room.

Then give the new format to your users. Yes, they only need 4 subjects per day, so they can leave the other columns empty on those days. Tell them to use it.

[Excel Trier]

Hi JBeaucaire. Thank you for the response.

Unfortunately the data format and headings etc. are not controlled by me. I am simply trying to extract data from the spreadsheets that are sent to me. Instructing the makers of the data to use a different format is 100% impossible. (kind of a "mail room janitor vs national head office" kind of situation).
The sample spreadsheet I sent is only a minor sample of the kind of extraction I need to do. Things actually get more complicated. But the array formula I presently use handles it perfectly.

I was hoping there was a minor change that could be made to the original array formula to have it return a sum of all matches, instead of just the single first match it finds.

But thank you for taking the time to view and respond. Much appreciated.

[dredwolf]

Okay, your upload is in compatability mode (pre-2007 Excel) so try this:
add a couple of columns after column F (3 at least),In G2:

Please Login or Register  to view this content.

drag down to bottom of data, then across to column J, this creates helper columns for the following Array Formulas (must be entered using Cntrl+Shift+Enter,not just Enter):
(pre-2007 Excel) in R3:

Please Login or Register  to view this content.

Drag down (See .sol1)

(2007 & later) in R3:

Please Login or Register  to view this content.

Again, Must be array entered; Drag down (See .sol2; (Note that I put this formula in column S, Just for reference purposes))

Hope this helps

EDIT-
The best route is still what Sixthsense and JBeaucaire suggested, arrange it so the data for each subject goes into one column, you could probable avoid "array" formulas altogether then..

[Excel Trier]

Hi dredwolf,

Wow. That's great. Just one question, is it possible to make it work even if headings are also a number code (not text entry).

Please see attached file for an example. I changed yellow cells to a number code heading as an example.

Much appreciated.

[Excel Trier]

In addition to above comment, could it also work if a heading were an empty cell (or text, or number).

Regards.

[dredwolf]

See attached
Basically, I just changed the Formula in G2 to check the student name column for Blak cells, then used those to Identify the column header Rows
New Formula in G2:

Please Login or Register  to view this content.

Drag down and across
I also added some Blank subjects to show that it works for all 3 types of headers

Hope this helps

[Excel Trier]

dredwolf,

That's brilliant. Thank you so much.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.