Convert array formula to non-array formula in shared workbook

[Deutz]

Hi and thanks in advance,

I have a shared workbook with some SUMPRODUCT formulas, in the 'Finalised' column of my Stats table, which Excel is treating as array formulas (didn't know SUMPRODUCT was array) that count rows in my 'Data' sheet where the date in column N matchs the month and year in my Stats table. I would like to covert these formulas to non-array since I am unable to copy the values returned by the formulas without getting the error message: Cannot copy or move array entered formulas or data tables in a shared workbook.

=SUMPRODUCT(--(MONTH(Data!$N$2:$N$1000)=F44),--(YEAR(Data!$N$2:$N$1000)=H44))

Cheers
Deutz

 

[Deutz]

Hi and thanks in advance,

I have a shared workbook with some SUMPRODUCT formulas, in the 'Finalised' column of my Stats table, which Excel is treating as array formulas (didn't know SUMPRODUCT was array) that count rows in my 'Data' sheet where the date in column N matchs the month and year in my Stats table. I would like to covert these formulas to non-array since I am unable to copy the values returned by the formulas without getting the error message: Cannot copy or move array entered formulas or data tables in a shared workbook.

=SUMPRODUCT(--(MONTH(Data!$N$2:$N$1000)=F44),--(YEAR(Data!$N$2:$N$1000)=H44))

View attachment 42911

Cheers
Deutz

Just realised I copied the wrong formula to this post. this is the correct formula in would like to convert to non-array type: =SUMPRODUCT(--(MONTH(Data!$N$2:$N$1000)=MONTH(DATEVALUE(G45&"1"))),--(YEAR(Data!$N$2:$N$1000)=H45))

 

[Marcelo Branco]

Try COUNTIFS

M.

 

[Deutz]

Try COUNTIFS

M.

Ok, tried entering this but keep getting the generic 'something wrong with formula' message.

=COUNTIFS(TEXT(Data!$N$2:$N$1000,"mmmm"),G45,YEAR(Data!$N$2:$N$1000),H45)

 

[Phuoc]

Or try:

=SUMPRODUCT(--(TEXT(Data!$N$2:$N$1000,"mmmmyyyy")=F44&H44))

 

[Phuoc]

One way:

=COUNTIFS(Data!$N$2:$N$1000,">="&(F44&"1, "&H44),Data!$N$2:$N$1000,"<"&EDATE(F44&"1, "&H44,1))

 

[Marcelo Branco]

Ok, tried entering this but keep getting the generic 'something wrong with formula' message.

Try
=COUNTIFS(Data!$N$2:$N$1000,">="&DATE(H44,MONTH(DATEVALUE(F44&"1")),1),Data!$N$2:$N$1000,"<="&EOMONTH(DATE(H44,MONTH(DATEVALUE(F44&"1")),1),0))

M.

 

[Deutz]

Or try:

=SUMPRODUCT(--(TEXT(Data!$N$2:$N$1000,"mmmmyyyy")=F44&H44))

Thanks Phuoc,

Your SUMPRODUCT formula worked but not your COUNTIFS.

Cheers
Deutz

 

[Deutz]

Try
=COUNTIFS(Data!$N$2:$N$1000,">="&DATE(H44,MONTH(DATEVALUE(F44&"1")),1),Data!$N$2:$N$1000,"<="&EOMONTH(DATE(H44,MONTH(DATEVALUE(F44&"1")),1),0))

M.

Thanks Marcelo,

That worked

Cheers
Deutz

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.