CODEWITHSUNDEEP
excel
user1264074
user1264074

Reputation: 19

sum of all transactions if date = June

I have a list off transactions with the date in column B and total amount for the transaction in column I. I want to have a cell that contains sum total of all transactions in the month of June of year 2011. However, the dates in column B may not be all in June, so I need to filter months that are June and then add all the values for each transaction of that month.

My thinking, =IF(DATE(2011,06,*),SUM(VLOOKUP(.....), Null)) , =SUM(IF(B=Date(2011,06,*))

Can you help me out please?

Thank you

Upvotes: 1

Views: 5010

Answers (3)

barry houdini
barry houdini

Reputation: 46341

You can use SUMPRODUCT like this

=SUMPRODUCT((TEXT(B3:B63,"mmm-yyyy")="jun-2011")+0,I3:I63)

...or if you have Excel 2007 or later SUMIFS is probably preferable

=SUMIFS(I3:I63,B3:B63,">="&DATE(2011,6,1),B3:B63,"<"&DATE(2011,7,1))

or with first of relevant month in a cell, e.g. A1 you can make that more flexible

=SUMIFS(I3:I63,B3:B63,">="&A1,B3:B63,"<"&EOMONTH(A1,0)+1)

Upvotes: 3

markblandford
markblandford

Reputation: 3193

You should be able to do this with an Array Formula. Something like this where your transactions are in A2:A6 and your dates are in B2:B6. This will return the sum of all transactions in June 2011.

{=SUM((MONTH($B$2:$B$6)=6)*(YEAR($B$2:$B$6)=2011)*($A$2:$A$6))}

Upvotes: 1

Mayank
Mayank

Reputation: 8852

Tested code, works fine for me.

so if A1 = 06/01/2010

=if(MONTH(A1)=6, SUM(VLOOKUP(.....), Null), SUM(I(IF(B=Date(2011,06,)))))

Upvotes: 0

Related Questions