Excel/VBA problem

A

archived_user

New member
Joined
Dec 7, 2011
Messages
0
Reaction score
0
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.
 
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?
Click to expand...
“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.
 
Codtrawler87 wrote:
gj479 wrote:
Not sure what you mean criteria *2018*, did you try year() function?
Click to expand...
“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.
Click to expand...
When using the “*2018*” rule, does it have to read 2018? Or, can it be 8201 for example?
 
CEO10K-DAY wrote:
Codtrawler87 wrote:
gj479 wrote:
Not sure what you mean criteria *2018*, did you try year() function?
Click to expand...
“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.
Click to expand...
When using the “*2018*” rule, does it have to read 2018? Or, can it be 8201 for example?
Click to expand...
I believe it has to read 2018, but I’m not 100% sure.
 
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)
 
You must log in or register to reply here.
Back
Top