# SUMIFS Year&Month Criteria in One Cell

#### meamuka

##### New Member
Hi guys,

I'm trying to sum with the sumifs formula based on the following table:

 Criteria Range Sum Range Criteria Criteria SUMIFS Date Amount Year Month Sum 6 nov 16 23 2014 jan ? 8 dec 17 23 2016 sep ? 1 jan 14 44 2016 nov ? 2 sep 16 34 2017 dec ?

<tbody>
</tbody>

Anyone?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

if under "criteria month"

"jan" is a text string

=SUMPRODUCT(\$B\$2:\$B\$5*(MONTH(\$A\$2:\$A\$5)=MONTH(D2&0))*(YEAR(\$A\$2:\$A\$5)=C2))

if "jan" is a date (formatted as "mmm")

=SUMPRODUCT(\$B\$2:\$B\$5*(MONTH(\$A\$2:\$A\$5)=MONTH(D2))*(YEAR(\$A\$2:\$A\$5)=C2))

Regards

Last edited:
I tried something similar to SUMIFS(B3:B6,YEAR(A3:A6),C3,MONTH(A3:A6),D3) but this ain't working.

POST EDIT: I posted this before I saw your answer. Thanks a lot for that! Is there anyone who knows how to do it with sumifs? And yes, the date column is formatted as dates in excel.

Last edited:
Guys!

After much attempt I still can't figure out this problem. I have two separate sheets for solving this but it won't work. I am pretty sure the sumifs formula will do but it needs a twist somewhere!

Hi

as far as I know, in order to use SUMIFS, you would need a couple of service columns in order to extract YEAR and MONTH from column A.

If you give a look at the screentip that pops up when you're using SUMIFS you will see as argument "sum_ranges" and "criteria_ranges", ranges not arrays.

Sumifs cannot elaborate arrays of results as you could expect from

YEAR(A3:A6)

MONTH(A3:A6)

to be matched with criteria.

SUMPRODUCT is the right "househould appliance" to be exploited for these operations.

I hope to be proved wrong.

Regards

Last edited:
Once again thanks a lot Canapone!

I see you're definitely right. I managed to get the SUMPRODUCT working by using named ranges since the range reference (e.g. A3:A66) was giving me an issue if the cells were blank. I didn't use SUMPRODUCT that much before so I was hesitating on going for it. Thanks once more!

Ciao,

thanks for sharing feedback.

Is there anyone who knows how to do it with sumifs?

Hi, maybe you could give this a try:

Excel 2013
ABCDE
1Criteria RangeSum RangeCriteriaCriteriaSUMIFS
2DateAmountYearMonthSum
306-Nov-16232014jan59
408-Dec-17232016sep34
501-Jan-14442016nov23
602-Sep-16342017dec23
705-Jan-1415
Sheet1
Cell Formulas
RangeFormula
E3=SUMIFS(B:B,A:A,">="&D3&"-"&C3,A:A,"<="&EOMONTH(D3&"-"&C3,0))

@FormR "Brilliant!"

just to play with criteria, if you don't need to match any strange date

=SUMPRODUCT(SUMIF(A:A,ROW(\$1:\$31)&"-"&D3&"-"&C3,B:B))

=SUMPRODUCT(SUMIFS(B:B,A:A,ROW(\$1:\$31)&"-"&D3&"-"&C3))

could do the trick.

I did not find the way to use one criteria without SUMPRODUCT.

Last edited:
Hi Canapone,

Interesting approach - just a couple of comments.

1. Each instance of those options will effectively call the SUMIF(s) function 31 times, so will probably be slower to calculate than the single SUMIFS or even the SUMPRODUCT option.

2. Using "ROW(\$1:\$31)" is not robust, meaning if you insert rows at row 1 of the worksheet the formula will no longer work as intended. This can be dealt with, but given the first comment I'd probably stick with one of the other options.

FWIW - I over did the date coercion and my suggestion can be reduced to:

=SUMIFS(B:B,A:A,">="&D3&C3,A:A,"<="&EOMONTH(D3&C3,0))

Replies
3
Views
116
Replies
3
Views
1K
Replies
2
Views
181
Replies
2
Views
206
Replies
2
Views
201

1,196,277
Messages
6,014,412
Members
441,818
Latest member
itsfaisalkhalid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back