CODEWITHSUNDEEP
excelexcel-formula
Fahim Parkar
Fahim Parkar

Reputation: 31647

do sum filtered by year

Hi want to create some report using data what I have. Below is what I have.

Date       Sold
22-Oct-11   22
23-Oct-11   28
24-Oct-11   10
22-Oct-12   22
23-Oct-12   28
24-Oct-12   10

What I want is create year and sold total as below.

Year   Sold
2011    60
2012    87

Any idea how to get this done using excel pre-defined function.

I know this can be done using MACRO, however I don't want to use MACRO.

Edit 1

I don't want to do this using Pivot too. ONLY Excel pre-defined functions.

Upvotes: 0

Views: 5646

Answers (5)

Valery
Valery

Reputation: 11

Try this formula: =SUMIF($A$2:$A$7;"=*11";$B$2:$B$7). I think it's possible to improve this formula, but I'm just a beginner.

Upvotes: 1

SeanC
SeanC

Reputation: 15923

With Excel 2007 (and later), you have access to the SUMIFS function Assuming your data is in columns A and B, in the order shown, then a function to add up all the data in 2011 would be

=SUMIFS(B:B,A:A,">="&DATE(2011,1,1),A:A,"<="&DATE(2011,12,31))

of course, you can reference a cell containing the year number in place of the 2011 to make it portable

Upvotes: 0

AleV
AleV

Reputation: 345

you can use a pivot table. put the date field on the left, right click and Group by year and put the sold field in the values area

Upvotes: 0

J&#252;ri Ruut
J&#252;ri Ruut

Reputation: 2530

It's possible to use an array formula (Ctrl-Shift-Enter in formula window to enter, the curled brackets will be inserted by Excel, not by user):

={SUM(IF(YEAR(A1:A4)=2010,1,0)*B1:B4)}

Dates are in column A, sold quantities in column B.

Upvotes: 1

podiluska
podiluska

Reputation: 51514

If you can add an extra column for =Year(date) then you can use Subtotals to calculate your results (Data-Subtotals)

Upvotes: 0

Related Questions