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]

See if this works for you:

=SUM(SUMIF($B$1:$B$4,$A$1:$A$4,$C$1:$C$4))
confirmed with Control+Shift+Enter when you put it in the formula bar.

 

[240racr]

See if this works for you:

=SUM(SUMIF($B$1:$B$4,$A$1:$A$4,$C$1:$C$4))
confirmed with Control+Shift+Enter when you put it in the formula bar.

Thank you Eric W!!! It's amazing how simple that ended up being.

 

[Eric W]

You're welcome!

 

[240racr]

You're welcome!

Eric, may I trouble you with another question?

That formula works great, but now I need it to be part of a larger formula. I thought it would be pretty straight forward (which it may be), but I'm not getting anything to work.

What I need is something like this (where {=SUM(SUMIF($E$2:$E$57,$A$2:$A$57,$F$2:$F$57))} =the name hrs )

=SUM((SUMIF(Book_Year,"<>")-(SUMIF(Book_year_sub,"<>"))-Sheet2!hrs))

I have 9 sheets that I need to reference based on a criteria in a cell (the SUM formula works, but not when I add in hr).

 

[Eric W]

I'm having a bit of trouble understanding the question. Could you show some data like you did before with the expected results?

 

[240racr]

I don't know where to start with showing data...there is so much going on. But I'll try to explain better.

I have a calendar that a lot of things update automatically based on Calendar!B2 (B2 is the year in string format that changes with a spin button). I have one cell that I want to keep track of absent time that needs to be made up.

Book_Year =

=IF(Calendar!$B$2=2017,'2017'!$F$2:'2017'!$F$57,IF(Calendar!$B$2=2018,'2018'!$F$2:'2018'!$F$57,IF(Calendar!$B$2=2019,'2019'!$F$2:'2019'!$F$57,IF(Calendar!$B$2=2020,'2020'!$F$2:'2020'!$F$57,IF(Calendar!$B$2=2021,'2021'!$F$2:'2021'!$F$57,IF(Calendar!$B$2=2022,'2022'!$F$2:'2022'!$F$57,IF(Calendar!$B$2=2023,'2023'!$F$2:'2023'!$F$57,IF(Calendar!$B$2=2024,'2024'!$F$2:'2024'!$F$57,IF(Calendar!$B$2=2025,'2025'!$F$2:'2025'!$F$57,"Calendar!"))))))))) the sum of F2:F57 on various sheets based on a cell value. (this is hrs absent)

Book_Year_Sub =

=IF(Calendar!$B$2=2017,'2017'!$H$2:'2017'!$H$57,IF(Calendar!$B$2=2018,'2018'!$H$2:'2018'!$H$57,IF(Calendar!$B$2=2019,'2019'!$H$2:'2019'!$H$57,IF(Calendar!$B$2=2020,'2020'!$H$2:'2020'!$H$57,IF(Calendar!$B$2=2021,'2021'!$H$2:'2021'!$H$57,IF(Calendar!$B$2=2022,'2022'!$H$2:'2022'!$H$57,IF(Calendar!$B$2=2023,'2023'!$H$2:'2023'!$H$57,IF(Calendar!$B$2=2024,'2024'!$H$2:'2024'!$H$57,IF(Calendar!$B$2=2025,'2025'!$H$2:'2025'!$H$57,"Calendar!"))))))))) the sum of H2:H57 on various sheets based on a cell value. (this is absent hrs made up)

Hrs ={=SUM(SUMIF($E$2:$E$57,$A$2:$A$57,$F$2:$F$57))}

So I have currently =(Total absent time) - (time made up)-(hours that are partial vacation hours) =SUM((SUMIF(Book_Year,"<>")-(SUMIF(Book_year_sub,"<>"))-Calendar.xlsm!Hrs))

This works for calculating everything from sheet 2017. The Calendar.xlsm! was auto-placed by excel, so I am not sure how this formula actually is working, but it does.

So what I need is, when the spin button changes the year (as a string), the Calendar.xlsm!Hrs part would equate to something like

Spin button text is 2017 = 2017!{=SUM(SUMIF($E$2:$E$57,$A$2:$A$57,$F$2:$F$57))}
Spin button text is 2017 = 2017!{=SUM(SUMIF($E$2:$E$57,$A$2:$A$57,$F$2:$F$57))}
Spin button text is 2017 = 2017!{=SUM(SUMIF($E$2:$E$57,$A$2:$A$57,$F$2:$F$57))}

Hope this convoluted post makes sense

 

[Eric W]

I've read this about 3 times, and I'm still pretty confused!

I have a few thoughts. First, your Book_Year formula can be greatly shortened to:

=INDIRECT(Calendar!$B$2&"!$F$2:$F$57")

Book_Year_Sub can be shortened the same way.

Next, I don't see the need for the surrounding SUM in this formula:

=SUM((SUMIF(Book_Year,"<>")-(SUMIF(Book_year_sub,"<>"))-Calendar.xlsm!Hrs))

just try:

=SUMIF(Book_Year,"<>")-SUMIF(Book_year_sub,"<>")-Calendar.xlsm!Hrs

Next, the Calendar.xlsm piece of that formula. That appears to indicate a sheet name. But if you want to change the sheet that the Hrs formula looks at, then you can just use INDIRECT as shown above:

Hrs ={=SUM(SUMIF(INDIRECT(Calendar!$B$2&"!$E$2:$E$57"),INDIRECT(Calendar!$B$2&"!$A$2:$A$57"),INDIRECT(Calendar!$B$2&"!$F$2:$F$57")))}

You can use defined names as above:

Hrs ={=SUM(SUMIF(INDIRECT(Calendar!$B$2&"!$E$2:$E$57"),INDIRECT(Calendar!$B$2&"!$A$2:$A$57"),Book_Year))}
Hrs ={=SUM(SUMIF(Date_1,Date_2,Book_Year))} - if you define Date_1 and Date_2

Let me know if you get anything useful from this!

 

[240racr]

Thanks Eric,

Yeah, I know its confusing. Hope this makes more sense. All three Names work perfectly by themselves. =SUM(SUMIF(Book_Year,"<>")-(SUMIF(Book_year_sub,"<>"))) Works perfectly by itself also. Problem is when combining all three.

Book_Year = Hrs missed
Book_year_sub = Hrs made up
Hrs = Summing up the count of Hrs missed only where Vacation Column & the first Date Column match (in example below matching dates and their corresponding values are larger to highlight that they match).

So result in example if Calendar!B2 was 2017 Then:
(4+2+3) - (2) - (4+2)
(9) - (2) - (6)
=1

So result in example if Calendar!B2 was 2018 Then:
(3+3+5) - (1+1) - (3+3)
(11) - (2) - (6)
=3

So result in example if Calendar!B2 was 2019 Then:
(3+6+8) - (4) - (3)
(17) - (4) - (3)
=10



[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Cell B2 (string value of 2017-2025)[/TD]
[TD]Spin button changes cell B2 to a value from 2017-2025[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Formula would go here. It would look at value in Cell B2 to point to which sheet to use. Then would calculate =SUM(SUMIF(Book_Year,"<>")-(SUMIF(Book_year_sub,"<>")-Hrs))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calendar
(sheet1)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[/TR]
[TR="class: grid"]
[TD]Vacation[/TD]
[TD]Absent[/TD]
[TD]Snow/Ice[/TD]
[TD]Travel[/TD]
[TD]Date[/TD]
[TD]Hrs missed[/TD]
[TD]Date[/TD]
[TD]Hrs made up/surplus[/TD]
[/TR]
[TR="class: grid"]
[TD]1/1/2017[/TD]
[TD="align: center"]1/3/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/3/2017[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1/20/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]1/2/2017[/TD]
[TD="align: center"]2/5/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/5/2017[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]1/3/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/8/2017[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]2/5/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]2/6/2017[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"]2017 (Sheet2)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[/TR]
[TR="class: grid"]
[TD]Vacation[/TD]
[TD]Absent[/TD]
[TD]Snow/Ice[/TD]
[TD]Travel[/TD]
[TD]Date[/TD]
[TD]Hrs missed[/TD]
[TD]Date[/TD]
[TD]Hrs made up/surplus[/TD]
[/TR]
[TR="class: grid"]
[TD]1/1/2018[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]1/2/2018[/TD]
[TD="align: center"]2/5/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/5/2018[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2/10/2018[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]1/3/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/1/2018[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]2/5/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]2/6/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"]2018 (Sheet3)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[/TR]
[TR="class: grid"]
[TD]Vacation[/TD]
[TD]Absent[/TD]
[TD]Snow/Ice[/TD]
[TD]Travel[/TD]
[TD]Date[/TD]
[TD]Hrs missed[/TD]
[TD]Date[/TD]
[TD]Hrs made up/surplus[/TD]
[/TR]
[TR="class: grid"]
[TD]1/10/2019[/TD]
[TD="align: center"]2/3/2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2/3/2019[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8/22/2019[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]1/11/2019[/TD]
[TD="align: center"]4/7/2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/7/2019[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]2/3/2019[/TD]
[TD="align: center"]4/8/2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/8/2019[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]4/7/2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD]6/6/2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR="class: grid"]
[TD="align: center"]2019 (Sheet3)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[240racr]

Hi Eric,

Finally found a hosting site I could access through my firewall.

The formula I need would be in AB2. As you can see, I currently have it working for only 2017.

This is one of several year sheets that the formula would dynamically look at. Every year sheet is the same layout. Cells I1:K9 are only there for development purposes. I highlighted matching dates just like in the previous post. Cell K3 is the result I'm looking for, but the formula would refer to cell Calendar!B2 on the picture above.

Cell K2 is just the result of SUM(F2:F6) - (H2) - (K3))