Sign up  |  Log in

Excel/VBA problem

So I’m trying to use a basic SUMIF function between two charts in excel. I’d like the function to sum all rows that has the year 2018 in the date column. When I write the SUMIF function with criteria “*2018*”, the function returns no value, but if I insert a specific date from the chart, f.ex “4.12.2018”, the function returns the appropriate value. I can’t for the life of me figure what I’m doing wrong.

If you're the first out the door, that's not called panicking

Schweser's upgraded learning platform and refreshed content are exactly what you need to prepare to pass the CFA® Program exam. Save 10% when you order a Premium Package for a limited time.

Send me excel sheet

Not sure what you mean criteria *2018*, did you try year() function?

Depends on what format the source data is in. If it’s a year, then “2018” would work as the search criteria. If the time data is in “MMDDYYYY” format, then you’ll need to create a separate column that converts that format to a simple 4 digit year using the YEAR function. This column will then be the criteria column for your SUMIF.

gj479 wrote:

Not sure what you mean criteria *2018*, did you try year() function?

“2018” = exact match

“*2018*”= contains 2018

I was able to go around the issue with a SUMIFS, so that the function sums the values if dates are between Jan 1st and Dec 31st of 20XX. 

If you're the first out the door, that's not called panicking

Codtrawler87 wrote:

gj479 wrote:

Not sure what you mean criteria *2018*, did you try year() function?

“2018” = exact match

“*2018*”= contains 2018

I was able to go around the issue with a SUMIFS, so that the function sums the values if dates are between Jan 1st and Dec 31st of 20XX. 

When using the “*2018*” rule, does it have to read 2018? Or, can it be 8201 for example?

¯\_(ツ)_/¯ It be like that sometimes.

CEO10K-DAY wrote:

Codtrawler87 wrote:

gj479 wrote:

Not sure what you mean criteria *2018*, did you try year() function?

“2018” = exact match

“*2018*”= contains 2018

I was able to go around the issue with a SUMIFS, so that the function sums the values if dates are between Jan 1st and Dec 31st of 20XX. 

When using the “*2018*” rule, does it have to read 2018? Or, can it be 8201 for example?

I believe it has to read 2018, but I’m not 100% sure. 

If you're the first out the door, that's not called panicking

Just use the “YEAR(date)” function within SUMIF. 

Let excel work for you rather than you working for excel :)

Hi.

You can also try formula SUMPRODUCT - very powerful one. But keep in mind, that it might make your file heavy if you apply it to a big number of cells/columns.

=SUMPRODUCT(–(YEAR($C$3:$C$19)=2018)*$D$3:$D$19)