Sum Multiple Categories Depending on Lookup Table

Hello and welcome to the forum.

Try this in C6:E6

Formula:

Please Login or Register  to view this content.

Is it possible to make something without the sumifs or is the and logic plus variety of bucket composition mean I am being a little too cute?

It is possible. It gets involved, though. In the attached it took me about an hour to work out this MMULT type solution.

Formula:

Please Login or Register  to view this content.

If you don't work with MMULT regularly it can be confusing to use ... and I use it quite a bit.

By the way SUMIF also works (see attached).

If it were me I would stick with the SUMIF(S) solution.

Just to learn/run through formula. Index the full data range, match returns the row number, leaving column zero or blank forces index to pull the entire row of data in the selected data range. Then it is the normal sumifs with a sumproduct to handle the array generated by the index.

The SUMPRODUCT is to handle the array returned by the SUMIFS (due to the multiple criteria output by INDEX you described). This is a point often missed when new to SUMIFS and arrays ... (been there, done that and on occasion still miss that detail to this day. )

A point worth mentioning if the ranges of data are dynamic. The IFs family of functions can handle whole column / row references with little noticeable performance impact. You would need to make sure the formula is not in the referenced range. See in Sheet1(2) of the attached.

Formula:

Please Login or Register  to view this content.

Thank you again. I appreciate you spending the time.

I'm going to use the Sumif/sumproducts -- I did make the sheet with full column references, but thank you for confirming that full column references aren't an issue.

I don't use MMULT at all. I did a little googling just to have some reference. Can you walk me through the formula.

On the back end, I see that index and match again pull the whole row of values. Comparing those values to the three buckets to generate true/false references and using the double unary to convert the true/false values to 1s and 0s. The 1s and 0s will be multiplied to the first array by the MMULT formula leaving me 0s and sum of the buckets that match the criteria.

I'm confused with the front end of the formula. I see an if with a hard coded array of 1 so that it multipllies true values. I don't understand what the transpose and N functions are doing. Maybe the N is creating leaving 1s and converting all none errors to 0?

Right now my workbook is basically three sheets. The report sheet with all the sumif formulas, a data dump sheet with all the raw data, and a list sheet where I have several named ranges and lookup tables.

I have four work books all with different projects (and groupings), but all with similar size and layout.

The Raw Data dump is 60K rows of data and 17 columns. The list sheet has 6 lookup tables and named ranges of various sizes (3-100 rows, 2-4 columns). The report sheet is 150 rows of sumproduct--sumifs and 25-40 columns columns. File size ranges from 3 - 5 MB. Right now if I apply a filter to the raw data table or insert a row in the report worksheet it takes 8-10 seconds for the change to take affect (I get the 4 threads calculating). This is fine for right now because I had a deadline, but going forward (and if other people start to use the workbook regularly) I would love to cut the process time down a little.

For transparency and simplisity I put every account number in the compnay in the rows so that no matter what I could see full detail and have a sense of history. Some accounts might be only used one a year, some accounts might be special for only xyz project, I didn't use a privot table because I didn't want my rows and list of account numbers to change as accounts were used or not used between periods. If nothing happened I still want to see the account number with a zero.

Do you have any suggestions? Is my calculation time typical considering the amount of data and formulas? Would a pivot table on a new sheet and GetPivotData formula on the main report sheet be a better option than all the sumproduct/sumifs. Is it simply a matter of breaking up the reports into small pieces and calculating less data?

All I can say is that the source layout seemed awkward to me. Unfortunately I don't have any suggestions for how to do that otherwise

I do have some thoughts though. I noticed that the buckets do not have a "reserved" location in the Lookup table. Notice for example that A and B have consistent columns but C does not. I don't know if making those locations consistent would facilitate efficiency or speed, but it couldn't hurt. It would definitely open more possibilities. I know the biggest challenge I found was in accommodating that layout.

RE: the TRANSPOSE N functions. TRANSPOSE does what it says ... it transposes a column of data into a row. MMULT needs that arrangement in the first argument.
Normally TRANSPOSE requires commitment with Ctrl + Shift + Enter ... ie it is an array function. The N(IF({1}, part makes commitment CSE unnecessary so can be eliminated if you like. Since this will be a shared project I included it in case others were not familiar with array CSE formulas. That last is something I gleaned from the Excel "underground". If you are interested in knowing more (and who isn't? LOL) XOR LX's blog is full of tidbits like that. Scroll down to the comments section there for more.

Update: Regarding the MMULT formula. I got carried away and over complicated things. This would be sufficient instead.

Formula:

Please Login or Register  to view this content.

My apologies. I don't know why I didn't think of it before.