SUM values based on matching cells

[240racr]

I've been fighting with trying to get this to work and have been scouring the internet for answers.

I am needing find the SUM of Column C values where Columns A & B match and that SUM would be in D1 as in the example below (not a formula in D1 Example).

Currently I have the following formula the works, but it requires it to be pasted in multiple cells, which ends up being a column of matching values (not what I want, I want the sum of matching values in one cell).

{=SUM(IF($B$1:$B$57=$A1,IF($A$1:$A$57=$B1,$C$1:$C$57)))}


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/2017[/TD]
[TD]1/1/2017[/TD]
[TD]4[/TD]
[TD]SUM = 10 (ie. (A1&B1=C4)+(A3&B2=C2)+(A4&B3=C4))[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/2/2017[/TD]
[TD]1/3/2017[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/3/2017[/TD]
[TD]1/4/2017[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/4/2017[/TD]
[TD]2/1/2017[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If what I need ends up needing to be done in VBA, that fine, I just need to know how. (Note: I don't know VBA)

Thank you

 

[Eric W]

After spending a fair amount of time looking at this, I think I understand what you want. I also believe that the formulas you are using are overcomplicated.

On your 2017 sheet, put this array formula in K3:

=SUM(F2:F57)-SUM(H2:H57)-SUM(SUMIF(E2:E57,A2:A57,F2:F57))

use Control+Shift+Enter

If I understand correctly, that's the exact equivalent of what you have now, using data from the 2017 sheet. SUMIF is really not needed here, since the SUMIF criteria is "<>", meaning "is not empty", but a standard SUM ignores empty cells as a matter of course.

You could also use this non-array formula

=SUMPRODUCT(F2:F57,--(ISERROR(MATCH(E2:E57,A2:A57,0))))-SUM(H2:H57)

slightly longer, but it doesn't need the CSE, and it only uses each range once. (The importance of that will be evident shortly.)

Let's say you put one of those formulas on all of your year sheets 2017 to 2025 in K3. Then the formula you'd want on the Calendar sheet in AB2 would simply be:

=INDIRECT(B2&"!K3")

You can't get much simpler than that!

However, if you don't want to keep all the K3 formulas (I'd recommend you keep them, but that's up to you), and you want to generate the value in AB2 directly from the data in the year sheets, you can take the formula from K3, and put each range in an INDIRECT which allows you to put the sheet name in front of the range.

Using the first K3 formula to put in Calendar!AB2 would be:

=SUM(INDIRECT(B2&"!F2:F57"))-SUM(INDIRECT(B2&"!H2:H57"))-SUM(SUMIF(INDIRECT(B2&"!E2:E57"),INDIRECT(B2&"!A2:A57"),INDIRECT(B2&"!F2:F57")))

with Control+Shift+Enter

Using the second K3 formula to put in Calendar!AB2 would be:

=SUMPRODUCT(INDIRECT(B2&"!F2:F57"),--(ISERROR(MATCH(INDIRECT(B2&"!E2:E57"),INDIRECT(B2&"!A2:A57"),0))))-SUM(INDIRECT(B2&"!H2:H57"))

The fact that I use one less range in this formula means one less INDIRECT, meaning it's now slightly shorter than the first formula.


Hope this helps!

 

[240racr]

Thank you so much for taking the time to help me with this Eric! I think I see what you're doing here and I will do some testing and let you know the outcome. It may be a few days as I don't have time to work on this in the immediate future. Thank you again!

 

[240racr]

Eric, I really appreciate your help. Your solutions are quite simple. I find it easy to overthink formulas sometimes. Everything is working as intended!! Thanks again!

After spending a fair amount of time looking at this, I think I understand what you want. I also believe that the formulas you are using are overcomplicated.

On your 2017 sheet, put this array formula in K3:

=SUM(F2:F57)-SUM(H2:H57)-SUM(SUMIF(E2:E57,A2:A57,F2:F57))

use Control+Shift+Enter

If I understand correctly, that's the exact equivalent of what you have now, using data from the 2017 sheet. SUMIF is really not needed here, since the SUMIF criteria is "<>", meaning "is not empty", but a standard SUM ignores empty cells as a matter of course.

You could also use this non-array formula

=SUMPRODUCT(F2:F57,--(ISERROR(MATCH(E2:E57,A2:A57,0))))-SUM(H2:H57)

slightly longer, but it doesn't need the CSE, and it only uses each range once. (The importance of that will be evident shortly.)

Let's say you put one of those formulas on all of your year sheets 2017 to 2025 in K3. Then the formula you'd want on the Calendar sheet in AB2 would simply be:

=INDIRECT(B2&"!K3")

You can't get much simpler than that!

However, if you don't want to keep all the K3 formulas (I'd recommend you keep them, but that's up to you), and you want to generate the value in AB2 directly from the data in the year sheets, you can take the formula from K3, and put each range in an INDIRECT which allows you to put the sheet name in front of the range.

Using the first K3 formula to put in Calendar!AB2 would be:

=SUM(INDIRECT(B2&"!F2:F57"))-SUM(INDIRECT(B2&"!H2:H57"))-SUM(SUMIF(INDIRECT(B2&"!E2:E57"),INDIRECT(B2&"!A2:A57"),INDIRECT(B2&"!F2:F57")))

with Control+Shift+Enter

Using the second K3 formula to put in Calendar!AB2 would be:

=SUMPRODUCT(INDIRECT(B2&"!F2:F57"),--(ISERROR(MATCH(INDIRECT(B2&"!E2:E57"),INDIRECT(B2&"!A2:A57"),0))))-SUM(INDIRECT(B2&"!H2:H57"))

The fact that I use one less range in this formula means one less INDIRECT, meaning it's now slightly shorter than the first formula.


Hope this helps!

 

[Eric W]

I'm glad you called my solutions simple, and not me!

I've found that crafting a short, understandable function that does what it's supposed to is really an art. I can't count the number of times I create a formula, then see someone else with a better one. Then I do a head-slap and cry D'oh!

In any event, I'm glad you got everything working! Let us know if you have any more questions.