Summing Values for specific months and years

[Catflappo]

Hi,

I will set a base example first:


9 Nov 2012 Apple 5
12 Dec 2012 Apple 3
14 January 2013 Banana 8
17 January 2013 Apple 6
20 January 2014 Apple 3

I would like a sumifs formula please that will help me only add the results for Apple - as follows:

Nov 2012 5
Dec 2012 3
Jan 2013 6 (i.e. does not include Banana - and also does not include Jan 14 Apple)

Although I have experience with sumifs I have not been able to use the correct syntax to identify the month AND year.

Ultimately I want to look through many hundreds of dates, and add anything that fits two criteria - 1) a 'name' criteria, and 2) it falls within a certainly month and year.

Thanks for your help - I can supply the actual spreadsheet to anyone who is able to provide genuine assistance, if that is easier.

Kind regards

Ed

[FDibbins]

hi and welcome to the forum

Seems to me you already know what you want?

=sumifs(sumrange,fruit-range,"apples",daterange,">="&A1,daterange,"<="&B1)

Where A1 and B1 contains the date ranges

[Catflappo]

Thanks very much for your reply - yes, I do 1/2 know what I need to do but not 100%!

I should have said (I think - as it may make a difference):

The range of dates I am searching through are in DD/MM/YYYY format, where as the dates I am searching FOR are in MMM/YY format.

6/02/2014
12/02/2014
13/02/2014
27/02/2013
18/02/2014
21/02/2014
12/03/2014
14/03/2014


Nov-13
Dec-13
Jan-14
Feb-14
Mar-14

So in the example you have given - thank you - is dateA1 Feb-14 and dateB1 Mar-14? (and so on)

Or does the formula need to be adjusted to account for this?

Thanks again,

Ed

[FDibbins]

Real dates are acutally just a (now) 5-digit number representing the number of days passed since 1/1/1900, so the actual format doesnt really make a difference.

What will make a difference, is if the 2nd set of dates are not real dates, but text that looks like a date.

Can you upload a sample workbook, showing what you have and what you want?

[Catflappo]

Thanks for your help.....as I have said on the spreadsheet - I have just put any old thing in for the second set of criteria (date) as that is what I am trying to correct.

As I said earlier, it needs to ensure it has the correct month and year, not just the correct month.

Cheers,

Ed

(It is a list of dividend payments for various stocks).

[FDibbins]

Based on your sample data, try this, copied down and across...
=SUMIFS($C$3:$C$22,$A$3:$A$22,">="&$A28,$A$3:$A$22,"<"&EDATE($A28,1),$B$3:$B$22,B$27)

[Catflappo]

Great - working perfectly! Took a wee bit of getting in properly as the data was in a separate worksheet - but yes it seems to work great.

So THANK YOU very much for your great help - I'll try and find something more taxing for you next time!

Regards
Ed