Excel sum based on lookup of code and values in another table

up vote
0
down vote

favorite

Given 2 named tables in Excel 2013 (or higher):

tblInvoice

ID    InvRef    Total

1     I/123        45

2     I/234         8

tblDeliveries

ID    InvRef    Amt

1     I/123      10

2     I/123      15

3     I/123      20

4     I/234       5

5     I/234       3

How can we get the tblInvoice[Total] to compute automatically using an Excel formula? i.e. in pseudocode:

 tblDeliveries[Total] = SUM(tblDeliveries[Amt] WHERE MATCH InvRef)

I have tried this Excel formula in tblInvoice[InvTotal] but it is returning an incorrect value:

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[InvRef],tblDeliveries[Amt]))

Also tried swapping the first and second parameters. Produces a different amount, but still incorrect:

=SUMPRODUCT(SUMIF([InvRef],tblDeliveries[InvRef],tblDeliveries[Amt]))

If relevant, it is assumed that there is a 1:N relationship from tblInvoice[InvRef]:tblDeliveries[InvRef] and that tblInvoice[InvRef] is UNIQUE.

asked Nov 10 at 14:35

Adam

7781436

add a comment |

up vote
0
down vote

favorite

Given 2 named tables in Excel 2013 (or higher):

tblInvoice

ID    InvRef    Total

1     I/123        45

2     I/234         8

tblDeliveries

ID    InvRef    Amt

1     I/123      10

2     I/123      15

3     I/123      20

4     I/234       5

5     I/234       3

How can we get the tblInvoice[Total] to compute automatically using an Excel formula? i.e. in pseudocode:

 tblDeliveries[Total] = SUM(tblDeliveries[Amt] WHERE MATCH InvRef)

I have tried this Excel formula in tblInvoice[InvTotal] but it is returning an incorrect value:

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[InvRef],tblDeliveries[Amt]))

Also tried swapping the first and second parameters. Produces a different amount, but still incorrect:

=SUMPRODUCT(SUMIF([InvRef],tblDeliveries[InvRef],tblDeliveries[Amt]))

If relevant, it is assumed that there is a 1:N relationship from tblInvoice[InvRef]:tblDeliveries[InvRef] and that tblInvoice[InvRef] is UNIQUE.

asked Nov 10 at 14:35

Adam

7781436

add a comment |

up vote
0
down vote

favorite

Given 2 named tables in Excel 2013 (or higher):

tblInvoice

ID    InvRef    Total

1     I/123        45

2     I/234         8

tblDeliveries

ID    InvRef    Amt

1     I/123      10

2     I/123      15

3     I/123      20

4     I/234       5

5     I/234       3

How can we get the tblInvoice[Total] to compute automatically using an Excel formula? i.e. in pseudocode:

 tblDeliveries[Total] = SUM(tblDeliveries[Amt] WHERE MATCH InvRef)

I have tried this Excel formula in tblInvoice[InvTotal] but it is returning an incorrect value:

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[InvRef],tblDeliveries[Amt]))

Also tried swapping the first and second parameters. Produces a different amount, but still incorrect:

=SUMPRODUCT(SUMIF([InvRef],tblDeliveries[InvRef],tblDeliveries[Amt]))

If relevant, it is assumed that there is a 1:N relationship from tblInvoice[InvRef]:tblDeliveries[InvRef] and that tblInvoice[InvRef] is UNIQUE.

asked Nov 10 at 14:35

Adam

7781436

Given 2 named tables in Excel 2013 (or higher):

tblInvoice

ID    InvRef    Total

1     I/123        45

2     I/234         8

tblDeliveries

ID    InvRef    Amt

1     I/123      10

2     I/123      15

3     I/123      20

4     I/234       5

5     I/234       3

How can we get the tblInvoice[Total] to compute automatically using an Excel formula? i.e. in pseudocode:

 tblDeliveries[Total] = SUM(tblDeliveries[Amt] WHERE MATCH InvRef)

I have tried this Excel formula in tblInvoice[InvTotal] but it is returning an incorrect value:

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[InvRef],tblDeliveries[Amt]))

Also tried swapping the first and second parameters. Produces a different amount, but still incorrect:

=SUMPRODUCT(SUMIF([InvRef],tblDeliveries[InvRef],tblDeliveries[Amt]))

If relevant, it is assumed that there is a 1:N relationship from tblInvoice[InvRef]:tblDeliveries[InvRef] and that tblInvoice[InvRef] is UNIQUE.

excel excel-formula excel-2013 excel-2016

asked Nov 10 at 14:35

Adam

7781436

asked Nov 10 at 14:35

Adam

7781436

asked Nov 10 at 14:35

Adam

7781436

asked Nov 10 at 14:35

Adam

7781436

asked Nov 10 at 14:35

Adam

7781436

add a comment |

1 Answer
1

active

oldest

votes

up vote
2
down vote

accepted

The syntax is incorrect for what you require.

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[@InvRef],tblDeliveries[Amt]))

The @ is the crucial difference.

Regards

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

1

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53239987%2fexcel-sum-based-on-lookup-of-code-and-values-in-another-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
2
down vote

accepted

The syntax is incorrect for what you require.

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[@InvRef],tblDeliveries[Amt]))

The @ is the crucial difference.

Regards

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

1

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

add a comment |

up vote
2
down vote

accepted

The syntax is incorrect for what you require.

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[@InvRef],tblDeliveries[Amt]))

The @ is the crucial difference.

Regards

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

1

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

add a comment |

up vote
2
down vote

accepted

The syntax is incorrect for what you require.

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[@InvRef],tblDeliveries[Amt]))

The @ is the crucial difference.

Regards

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

The syntax is incorrect for what you require.

=SUMPRODUCT(SUMIF(tblDeliveries[InvRef],[@InvRef],tblDeliveries[Amt]))

The @ is the crucial difference.

Regards

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

edited Nov 10 at 16:27

answered Nov 10 at 14:50

XOR LX

6,9891813

answered Nov 10 at 14:50

XOR LX

6,9891813

answered Nov 10 at 14:50

XOR LX

6,9891813

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

1

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

add a comment |

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

1

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

I am not used to table references. Can you explain what the @ does?
– Forward Ed
Nov 10 at 15:47

It instructs the formula to look at the value in the referenced column within the same row as that in which the formula resides. Without it, you are referencing the entire column.
– XOR LX
Nov 10 at 15:49

Marvellous!!! Thank you so much!!! :)
– Adam
Nov 10 at 16:05

You're very welcome!
– XOR LX
Nov 10 at 16:26

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Name

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky