We help IT Professionals succeed at work.

Wildcard within SUMIF on a date

MPWOOD
MPWOOD asked
on
2,252 Views
Last Modified: 2012-02-14
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?
Comment
Watch Question

Commented:
why not use the MONTH operator

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

Author

Commented:
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 HusainEngineer
CERTIFIED EXPERT

Commented:
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)))
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

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

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.