Excel Formula Help 1

[arvarr]

Hi
I need some help with an Excel formula.

I have used the sumproduct formula to add up the respective company code amount.
Each company code has a percentage split and I'm not sure if the sumproduct formula can accomodate it.

Test File uploaded with expected results.

Thanks,
arv

 

[SkipVought]

Hi,

Your example is confusing.

I ASSUME that sheets Results & Table are source data.

If that is true, what defines this: that Company A is associated with those two codes and Company 3 a third?
[pre]
Code Code Code Company
123 456 A
789 B[/pre]

If that, indeed is to be considered source data, then it all must be properly contained in a table like...
[pre]
Code Company
123 A
456 A
789 B[/pre]

...AND does Code 789 get 0% since it does not appear in the Table sheet?

So before we progress further, please clarify.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Correct. Results equates to the source data for the amount. Table contains the percentage split.

Sheet1 = Company A comprise of code 123 and 456
In my real data, there is no company A, just 123 and 456.
I have added company A as grouping for this purpose. The formula should really be driven by the code 123 and 456...

Sheet1 - cell G11 and G12 are my expected results.

As a workaround, I am using the following formula but it gets very lengthy as i have more code to include.
=SUMPRODUCT((Results!$A$2:$A$7=$A3)*(Results!$B$2:$B$7))*SUMPRODUCT((Table!$A$3:$A$4=$A3)*(Table!$C$3:$C$4))+SUMPRODUCT((Results!$A$2:$A$7=$B3)*(Results!$B$2:$B$7))*SUMPRODUCT((Table!$A$3:$A$4=$B3)*(Table!$C$3:$C$4))

Thanks,
arv

 

[SkipVought]

First, you absolutely MUST use Structured Tables! Insert > Tables > Table

I converted ALL your tables.
Sheet Name
Table Table1
Results Table2
Sheet1 Table3 & Table4

Here's a screenshot of my results showing formulas...

You're not gonna get data strung together using formulas. That takes VBA code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Thanks for the info above.

In my real data, there is no company A, just 123 and 456.
I have added company A as grouping for this purpose. The formula should really be driven by the code 123 and 456...

Sheet1 - Remove data in E2:E4
Instead of looking up the company, Let the formula lookup the code (cell A3 and B3).
Is this feasible?

Thanks,
arv

 

[SkipVought]

Call it a Group rather than a company. Its a helper table. That's what Table3 is.

Horizontal lists are not how databases work. Excel's array formulas, use database-like features/tables to leverage their effectiveness, not horizontal lists.

Look how unintelligible and "wordy" your attempts are compared to the terse, compact tabular formulas in my solution. Combo may even be able to give you a better solution using Power Query.

BTW, one of the reasons for Structured Tables is that as your data expands in volume, the formulas never need to change to accommodate a different range of rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Thanks again for the prompt response.

Correct, it's not as effective as your set hence attempting to see if there's any solution around it.

Thanks,
arv

 

[SkipVought]

My column I and following totally replaces what's in columns A:G.

Whatever groupings you want, needs to be represented in columns not rows for Excel array formulas to work although technically, the table could have looked like this...

[pre]
Code 123 457 789
Grp A A B
[/pre]
...BUT your formula would need A1 notation and it would greatly expand and confuse my existing formulas.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!