SUMIFS Year&Month Criteria in One Cell

[meamuka]

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?

 

[Canapone]

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

 

[meamuka]

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.

 

[meamuka]

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!

 

[Canapone]

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

 

[meamuka]

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!

 

[Canapone]

Ciao,

thanks for sharing feedback.

 

[FormR]

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

Hi, maybe you could give this a try:

Excel 2013
	A	B	C	D	E
1	Criteria Range	Sum Range	Criteria	Criteria	SUMIFS
2	Date	Amount	Year	Month	Sum
3	06-Nov-16	23	2014	jan	59
4	08-Dec-17	23	2016	sep	34
5	01-Jan-14	44	2016	nov	23
6	02-Sep-16	34	2017	dec	23
7	05-Jan-14	15
Sheet1

Cell Formulas
Range	Formula
E3	=SUMIFS(B:B,A:A,">="&D3&"-"&C3,A:A,"<="&EOMONTH(D3&"-"&C3,0))

 

[Canapone]

@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.

 

[FormR]

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))