Sumif(Text(Range,"mmmm"),"January",Range) does not work

[kornsatu]

Hi,

I have 2-column data record (hourly data in 1 year = 8760 rows), for example:

Column 1 Column 2
1/1/2018 00:00 15
1/1/2018 01:00 20
1/1/2018 02:00 5
.
.
.
31/12/2018 23:00 20


Then, I would like to summarize to monthly value.
What I use is
=sumif(text(A2:A8761,"mmmm"),"January",B2:B8761)

it seems to be not working, I don't understand why?

However, I can use {=sum(if(text(A2:A8761,"mmmm")="January",B2:B8761))} which is in this case working fine.
But I would like to know why sumif function does not work

Thank you for your help

 

[Aladin Akyurek]

Try this...

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

where E1 = 2018-01-01 which is a first day date.

 

[Tetra201]

The formula from Post # 2 would omit all the data between 31/1/2018 00:01 and 31/1/2018 23:59. The correct formula is:

=SUMIFS(B:B,A:A,">="&E1,A:A,"<"&EDATE(E1,1))

 

[pgc01]

...
What I use is
=sumif(text(A2:A8761,"mmmm"),"January",B2:B8761)
...
But I would like to know why sumif function does not work
...

If you check the help on SumIf(), you'll see:

[h=2]Syntax[/h]SUMIF(range, criteria, [sum_range])

You are not using a range as the first parameter.

 

[kornsatu]

@Aladin Akyurek : Thank you for your help
@Tetra201 : you are absolutely right. Thanks!

@pgc01 : As a first parameter, should text(A2:A8761,"mmmm") return Range output? or I understand wrongly

 

[pgc01]

@pgc01 : As a first parameter, should text(A2:A8761,"mmmm") return Range output?

TEXT() returns a Text value, not a Range value, and so cannot be used as the first parameter of the SumIf() function.

 

[Aladin Akyurek]

@Aladin Akyurek : Thank you for your help
@Tetra201 : you are absolutely right. Thanks!

@pgc01 : As a first parameter, should text(A2:A8761,"mmmm") return Range output? or I understand wrongly

Not so fast. I apparently overlooked the time bit in column A... A simple tweak takes care of that:


=SUMIFS(B:B,A:A,">="&E1,A:A,"<"&EOMONTH(E1,0)+1)

 

[kornsatu]

TEXT() returns a Text value, not a Range value, and so cannot be used as the first parameter of the SumIf() function.

Even I input Range in Text function like TEXT(A2:A8761,""), doesn't it return range output?

Thank you

 

[Aladin Akyurek]

Even I input Range in Text function like TEXT(A2:A8761,""), doesn't it return range output?

Thank you

TEXT manipulates the range which is then fed to SUMIFS. The latter does not understand the TEXT result (not designed to understand such).

 

[pgc01]

Even I input Range in Text function like TEXT(A2:A8761,""), doesn't it return range output?

Yes, it returns the range output as text. That's not what SumIf() requires.

SumIf() requires the reference to the range.

Ex.:
A1:C1 have 1,2,3

SumIf(A1:C1,...,...) is OK, the parameter has a reference to the range

SumIf({1,2,3},...,...) does not work. The parameter has the values of the range, not the reference to the range