SUMPRODUCT / SUMIF based on both row and column criteria

Hi there,

I have been searching for an answer to this problem for a few hours now, however without success.

Basically, I am looking for a formula that can sum a range based on two criteria;

• (1) Columns that contain one of multiple headers (looked up from a separate list)
• (2) Rows that contain a specific company name

I have enclosed a detailed description and example of the problem, which should clarify any uncertainties from the description above. The core of the problem is that I would like to avoid summing multiple SUMPRODUCT / SUMIF formulas (since the real case can include a fairly large amount of columns).

Thank you very much in advance. Any help is appreciated.

Best,
Phil

Thanks, Xlent, worked perfectly in the sample.

However, once copied to my full data set, I get the error that "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".

Are you familiar with a workaround this issue? The real data set should be able to handle approximately range A1:DA20000. Further, is there any way to make the formula lighter, since it really slows down the workbook (and the formula will be needed about 10 different places in the workbook).

I see your point. I found an alternative method using a dynamic range (as data will usually contain much fewer than 20000 rows). Thanks for all the help, excellent stuff.

One final question; How do I modify the SUM PRODUCT formula to sum the characters in the data set from right until a certain character (in this case "/")? For instance, some data is given as "Category/Topic/40%", for which I am looking for a separate formula (i.e. not an extension of the previous formula that allows it to handle such cases).

VALUE(TRIM(RIGHT(SUBSTITUTE(A2;"/";REPT(" ";100));100))) seems to do the extraction part, however, I am not sure how to incorporate it into the SUM PRODUCT formula from before without getting errors.

Yes, a DNR will help but only to a point.... and ensure you're not using OFFSET to create it else you will simply compound your problems (if running on Auto Calc)

In terms of the latest issue - well, given the nature of your data (i.e. you don't know where the numbers are, 100% of the time) you have a problem if you explicitly coerce as-is.

The below would probably work for you -- again, references old sample range so modify to suit requirements and locale etc...

Formula:

Please Login or Register  to view this content.

I've attached working example, for ease of reference / testing.

The prior suggestion should work, and the overhead should not prove that significant in the bigger scheme of things so I'd be inclined to use it as it should prove pretty robust.

If you're saying your summation range, in this latest example, is the only column to be aggregated and whose position is fixed then that would likely warrant an altogether different approach from that back in post 2, and so would likely warrant a new thread; i.e. if your original question has been answered, I hope it has, please mark as Solved [see FAQ if unclear on steps].

Thanks,

Originally Posted by Phil123456789

EDIT: It seems the above formula slows down the calculations significantly in the full data set, so if possible, I think something similar to VALUE(TRIM(RIGHT(SUBSTITUTE($A$2:$H$16;"/";REPT(" ";100));100))) would be a better fit, as it requires fewer searches (and as I know the relevant data will always be in the same position (from right to "/").

To be frank, without a sample file to reference file that accurately reflects your real requirement(s) it's hard to comment as to the feasibility of the above.

However, as noted earlier, your initial topic/question was answered - with additional suggestions from both myself & others re: follow up question as to how you might avoid use of SUMPRODUCT altogether.

I would suggest you now close this thread, create a new thread with your latest issue/requirement coupled with a sample file that reflects that setup.

Thanks jason.

However, please note that my biggest concern is to avoid helper columns and too long formulas, which your suggestion (if converted into a single formula by summing the sumifs) unfortunately would turn out to be, although it undoubtedly would increase calculation speed.

As I see it, the only real difference between the three formulas in D21:G26 is the project name (D20, E20, F20, and G20). Is there any way to modify your formula, so instead of having one formula for each project, they are combined into one, similarly to IF(COUNTIFS($K$5:$K$11;$B$19;$L$5:$L$11;D$20:G20);SUMIF($B$2:$B$16;$B21;INDEX($C$2:$F$16;0;MATCH(D$20;$C$1:$F$1;0)));""))?

I am aware that the above formula does not work, but it simplifies the formula.

As I am looking to pass the project on to somebody else once up and running, I am a little concerned that too long formulas will make it less intuitive and more difficult to understand for the next owner, considering they have not built the model from scratch.

Thanks in advance.

Here's an updated test file with a formula that looks like it works this time. Still volatile though.

Formula:

Please Login or Register  to view this content.

Thanks, really appreciate the effort and time.

It seems to be running OK with XLent's SUM PRODUCT formula and a non-volatile DNR for now, so hopefully it will continue to work once I add further elements to the model.

Best,
Phil