asked on

Wildcard within SUMIF on a date

I am trying to do a SUMIF a date falls in a certain month, i.e. 01/01/2011 I want to use a wildcard to sum only for January in this case .... so, my formula is :

=SUMIF(Sheet1!H:H,"*/01/2011",Sheet1!L:L)

However - it doesn't work.

Why not?

tigin44

why not use the MONTH operator

=SUMIF(Sheet1!H:H,MONTH("cell reference"),Sheet1!L:L)

MPWOOD

ASKER

Hi tigin44

thanks for this but I don't think I have it quite right.

for the month operator it requires the serial number, which I have as (1) in this case for January, but that doesn't seem to work as a 'contains' - and you have the suggested formula as ("cell_reference") as opposed to MONTH(1), so I don't quite understand this...

Can you clarify for me please?

Just to clarify my data :

dates are in H:H
sum values are in L:L
I want to sum everything from L where the date in H is a January month

Saqib Husain

You can try this formula

=SUMPRODUCT((MONTH(OFFSET(H1,0,0,COUNT(H:H)))=1)*(YEAR(OFFSET(H1,0,0,COUNT(H:H)))=2012)*OFFSET(L1,0,0,COUNT(H:H)))

barry houdini

If you have Excel 2007 or later you can use SUMIFS and then sum between two dates, e.g. with 1st of that month in K2 use

=SUMIFS(Sheet1!L:L,Sheet1!H:H,">="&K2,Sheet1!H:H,"<"&EOMONTH(K2,0)+1)

or in Excel 2003 you can subtract one SUMIF from the other, i.e.

=SUMIF(Sheet1!H:H,">="&K2,Sheet1!L:L)-SUMIF(Sheet1!H:H,">="&EOMONTH(K2,0)+1,Sheet1!L:L)

regards, barry

MPWOOD

ASKER

Hi Barry, I like your line of thinking on this, I'm on Excel 2010.

The last bit doesn't quite work : "<"&EOMONTH(K2,0)+1)

I'm thinking the E0 is spurious? The bit I just need to crack really is K2 + one month but with this formula : "<"&MONTH(K2)+1 I'm only succeeding to add 1 to K2 not a month to K2....

nearly there but can you get me over the line?

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barry houdini

See the attached example, I put the first of each month for the whole of the year in K2 down on summary sheet (formatted to show just month and day) and then used exactly that formula in L2 copied down, with some random dates and values in sheet1.

I'm assuming you have the calculations in a separate sheet from the data but it will work equally as well all in the same sheet.

regards, barry
sumifs.xlsx

MPWOOD

ASKER

I didn't know about EOMONTH function - great to learn new techniques, thanks Barry!

MPWOOD

ASKER

Barry,thanks for the follow up with the spreadsheet. That's helped me refine my (sorry, your!) formula to have the months listed in one column which makes coping the formula easier as it doesn't have to change. I did have a summary tab too anyway, so got it all up and running and sorted for a number of consecutive years - thanks again! Matt