CODEWITHSUNDEEP
excel
MathCurious314
MathCurious314

Reputation: 145

Finding the sum of dates using first column value

So I am working in excel and have two columns, as shown below.

enter image description here

What I wish to do is to able to get the sum of yearly deadlines for A, B and C.

So that:

Currently, I have been using a long approach. I use a Vlookup for each row getting a filtered table for each Variable. Then using that I filter out Dates using combination of Countif and Countifs. But the problem with that approach is that I end up getting huge rows and table of data in the end increasing the file size.

I have also tried to use sumproduct() but I do not have numbers so I can not find a sum.

Is there a smart way of doing it using one formula?

Thank you.

Upvotes: 0

Views: 31

Answers (1)

basic
basic

Reputation: 11968

Use SUMPRODUCT. For year/variable:

=SUMPRODUCT(($A$2:$A$13=E2)*(YEAR($B$2:$B$13)=$F$1))

for month/year/variable:

=SUMPRODUCT(($A$2:$A$13=$E2)*(YEAR($B$2:$B$13)=$F$1)*(MONTH($B$2:$B$13)=G$1))

enter image description here

Upvotes: 2

Related Questions