SUMIFS Formula on Excel

0
votes

I'm using a SUMIFS on Excel. One of my range columns is filled with dates formatted as "YYYY-MM". I want to sum only the values of another column that correspond with specific years, inclusive of all months. So for instance, I may have 2000-05, 2000-06, etc, and I wanna sum all the values that have "2000-" in them, no matter the MM value. Is there a way to do this?

2
excel-formula

2 Answers

2
votes

Assuming your data are in cells A1:A23, you can use the below to find the number of dates that are of the year 2000.

{=SUM((YEAR(A1:A23&"-01")=2000)*1)}

Note the curly brackets {} indicate that you must press cntrl+shift+enter when you finish typing the formula. (This is because it is an array formula).

The logic here is that A1:A23&"-01" will convert all the dates in those cells from, for example 2000-05 to 2000-05-01. Then the YEAR() function recognises this as a date and returns 2000.

The =2000 bit will return an array of booleans for example

TRUE
TRUE
TRUE
FALSE
FALSE
TRUE

We multiply this array by 1 to get

1
1
1
0
0
1

And we sum this to get the count of numbers with year = 2000.

1
votes

You need to do a small modification to the cells that contains the dates by adding the day to them - any day, you van choose 01 - for example, change 2000-05 to 2000-05-01, then you can use the formula as follows:

=SUMIFS(B1:B4,A1:A4,">=2000-01-01")