SumProduct - Summing Table with Dynamically Listed Criteria

[david@work]

Hello,

I'm having trouble using Sumproduct when I have:

1. Multiple Criteria
2. A dynamic list as one of the criteria
3. A table, not just a column, that needs summing

I've scoured threads but can't find my specific use case anywhere. Example worksheet attached that hopefully makes it simple to see what I'm trying to do. Note that from cell C11 to cell C15 there are dropdowns. That is essential to the problem.

Thanks a ton!

David

 

[bines53]

Hi David,

So I'll be able to understand, you can specify the desired values you attached example.

David

 

[david@work]

Sure thing! Thanks for looking David. I've expanded the workbook to include some examples and more context.

Thanks again,
David

 

[bines53]

Hi David,

The easiest solution and efficiently, in my opinion,

=SUM(INDEX($B$3:$M$7,N(IF(1,ohRemoveInvalidRows(MATCH(D14:D18,$A$3:$A$7,0)))),MATCH(C14,$B$2:$M$2)))

With CSE = Control + Shift + Enter

function =ohRemoveInvalidRows ,Here

https://sourceforge.net/projects/quantlib/files/QuantLibXL/1.7.0/
Choose QuantLibXL-1.7.0-bin.zip
Folder XLL ,There are two versions 32, 64, depending on what and version of Excel that you have.

David

 

[david@work]

Thanks David,

However, I'm unable to download any external add-ons to excel, as I'm on a corporate network. Is there any other way you could think of?

Let me know. Thanks again for looking at this!
David

 

[bines53]

Hi David,

Other way,
=SUMPRODUCT((B2:M2=C14)*(A3:A7=D14)*B3:M7)+SUMPRODUCT((B2:M2=C14)*(A3:A7=D15)*B3:M7)+SUMPRODUCT((B2:M2=C14)*(A3:A7=D16)*B3:M7)+SUMPRODUCT((B2:M2=C14)*(A3:A7=D17)*B3:M7)+SUMPRODUCT((B2:M2=C14)*(A3:A7=D18)*B3:M7)

If I do not use that function,=ohRemoveInvalidRows,
So,=INDEX($B$3:$M$7,N(IF(1,MATCH(D14:D18,$A$3:$A$7,0))),MATCH(C14,$B$2:$M$2)),
This is the solution I get,{845087;770606;697450;#N/A;#N/A},

You are looking for a dynamic solution, this solution different from static.

I personally use six different plug-ins, the functions built into Excel, you give only a partial solution.

There is no choice, if we want to work well and efficiently.

David

 

[david@work]

Thanks again for looking at this David,

Is there really no way to do it without extending the length of the syntax? I have gotten it to the point that the formula is recognizing the right rows, but because it is summing an array of data, rather than a single column, it errors out. Hoping for a simpler solution, but understand there are limitations!

Thanks again,
David

 

[bines53]

Hi David,

I'm sorry, I do not know how to help you beyond what I have proposed, should understand, Excel does not have all the tools for each problem.

David

 

[bines53]

Hi David,

I am pleased to announce that there is a solution,

=SUM(INDEX(COUNTIF(D14:D18,$A$3:$A$7)*$B$3:$M$7,,MATCH(C14,$B$2:$M$2))) for Scenario 1

=SUM(INDEX(COUNTIF(D22:D26,$A$3:$A$7)*$B$3:$M$7,,MATCH(C22,$B$2:$M$2))) for Scenario 2

=SUM(INDEX(COUNTIF(D30:D34,$A$3:$A$7)*$B$3:$M$7,,MATCH(C30,$B$2:$M$2))) for Scenario 3


David