summing data that meets certain conditions

[alegs34]

Hello all. I am having trouble coming up with a formula to fit this problem. Any suggestions would be appreciated. Here is my data

A B C D

A 2019-01-01 $10
A 2019-12-31 $10 $30 (sum of cell C2, C3 and C4)
A 2019-12-31 $10 $30 (sum of cell C2, C3 and C4)
A 2019-12-31 $10 $30 (sum of cell C2, C3 and C4)
B 2019-12-31 $10 $20 (sum of cell C5 and C6)
B 2019-12-31 $10 $20 (sum of cell C5 and C6)
C 2019-12-31 $10 $40 (sum of cell C7 C8 C9 and C10)
C 2019-12-31 $10 $40 (sum of cell C7 C8 C9 and C10)
C 2019-12-31 $10 $40 (sum of cell C7 C8 C9 and C10)
C 2019-12-31 $10 $40 (sum of cell C7 C8 C9 and C10)


I want to find a formula to put in column D such that it gives the sum of column C subject to the following conditions
1) the value of the date in column B is 2019-12-31
2) sum only the cells of column C where the same row's value for column A is the same as the column A value for the row the formula is in

I've shown in column D what the values of this hypothetically formula should be if it's working right.

Thank you for any insights you have!

[FDibbins]

Hi, welcome to teh forum

You seem to have left out the 1/1/2019 item?

Anyway, this should give you what you want...
D2=SUMIFS($C$2:$C$11,$A$2:$A$11,A2,$B$2:$B$11,B2)
copied down

[alegs34]

Thanks for your reply!

I did not forget the 1/1/2019 item, that one doesn't meet the first criteria (1) the value of the date in column B is 2019-12-31, so should be excluded

Your suggestion does work, however I was hoping for something more generalizable that I could drag down (my real data set has about 2000 unique values in column A so would be difficult to use this type of formula)

[FDibbins]

That formula will work no matter how long your data is - you just need to adjust the tange as needed
Or even use this...
=SUMIFS($C:$C,$A:$A,A2,$B:$B,B2)