Sum/If???

M

mariaw

Joined
Dec 11, 2006
Messages
10
Reaction score
0
Hi

I have put the following formula in:

=SUMIF(K1:K2014,"**/05/**",L1:L2014)

where column K is in date format dd/mm/yyyy and column L is a number column (representing costs). e.g. I want it to add up the costs for the month of May

It doesn't work...why not???

thanks

Maria
 
Well, first thing I noticed when trying out this formula was that excel interprets the dates as long values (ie. the date 12/05/2007 becomes 39214)... this is what's probably making the formula acting wrong. a solution would be to format the cells with dates into text and try again. This can be a long and tedious process if you have a lot of dates because you have to write the dates again after the format...

btw. you don't need two wildcards, just one on each side will do :)
=SUMIF(A5:A15,"*/05/*",B5:B15)
 
Hi Johz

Thanks; I tried converting the date format so it reads May 12, 2006 instead, and then changing the formula so it reads:

=SUMIF(K2:K2014,"May*",L2:L2014)

but this still doesn't work...

I see what you are saying about converting the dates to text, but unfortunately there are a lot of dates, and to have to type them in again would take a lot of time.

Do you have any other ideas?

Thanks

Maria
 
Ok, here's a way to do the conversion... you'll need to do it into a macro code module and select the dates you want converted and then start the macro:

Sub ChangeCellFormat()
Dim sDate As String
Dim r As Range
For Each r In Selection.Cells
sDate = Format(r.Value, "dd\/mm\/yyyy")
r.NumberFormat = "@"
r.Value = sDate
Next r
End Sub

It goes through the selected cells taking the date, converting it to dd/mm/yyyy -format, changing the format of the cell into text and then saving the (now text) date into the cell. Hope this helps!
 
Hi Johz

Wow that sounds fab....but I'm afraid I don't know how to set up a macro code module in Excel? I vaguely know how to do them in Access.....sorry can you help me out??!

thanks

Maria
 
k, here's how you first open VBE which you will use to set up the code:
press Alt + F11
you'll see a new window that has a treeview on the left, from there doubleclick on thisworkbook
then you copy&paste this into the right:

Sub ChangeCellFormat()
Dim sDate As String
Dim r As Range
For Each r In Selection.Cells
sDate = Format(r.Value, "dd\/mm\/yyyy")
r.NumberFormat = "@"
r.Value = sDate
Next r
End Sub

then you go back to excel, select the range of cells you wanted to reformat in you workbook and then push
Alt + F8
now you'll see a list of all the macros in your workbook, scroll down to "ChangeCellFormat" and run it.
Now the values should be reformatted into text.

Note that you can't undo changes done by macros, so you'll want to take a copy of the workbook BEFORE you add the macro code and do the changes, just in case anything goes wrong.:)

Also, after you've successfully executed the macro, you can go back to VBE (Alt + F11) and delete the code you added there. If you save the workbook without deleting the macro code, the macro will be saved as well and the next time you open the workbook, Excel will prompt you about macro safety... you probably don't want that, so delete the macro before saving.:)

Hope this helps!
 
Back
Top