What is Wrong with Sumproduct

[Faruk Hosen]

Hello,
My SUMPRODUCT formula is not getting result when working with big data.

Year
Month
Quarter
Range

Nothing is working.

=SUMPRODUCT((YEAR($A$2:$A$50000)=F$1)*($C$2:$C$50000))


Any solution?

Thanks.

 

[Hui]

F2: =SUMPRODUCT(--(YEAR($A$2:$A$50000)=F$1),$C$2:$C$50000)

I can only summize that the ranges are too large and hence the array formed by your formula maybe too large?

I have completed all the formula down to the weeks

There are a number of ways to define weeks, so you will need to use the appropriate formula

 

[NARAYANK991]

Hi ,

The problem is the cells which do not have numeric values in them ; they are not blank , but have either a space or a space-like character in them. This is the reason you cannot use the multiplicative version of SUMPRODUCT ; instead use the comma version , as suggested by Hui.

Narayan

 

[Faruk Hosen]

Hi ,

The problem is the cells which do not have numeric values in them ; they are not blank , but have either a space or a space-like character in them. This is the reason you cannot use the multiplicative version of SUMPRODUCT ; instead use the comma version , as suggested by Hui.

Narayan

Thanks to you and Hui.

My problem is almost solve with sumproduct except Quarter result of a year.

This is working
=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1})+(MONTH($A$2:$A$99999)={2})+(MONTH($A$2:$A$99999)={3}),$C$2:$C$99999)

But i want to make it simple. Like...
=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1,2,3}),$C$2:$C$99999)

What is going wrong?

Thanks and regards.

-Faruk Hosen

 

[Faruk Hosen]

F2: =SUMPRODUCT(--(YEAR($A$2:$A$50000)=F$1),$C$2:$C$50000)

I can only summize that the ranges are too large and hence the array formed by your formula maybe too large?

I have completed all the formula down to the weeks

There are a number of ways to define weeks, so you will need to use the appropriate formula

Thanks.
Would you please refer some links of week formula to complete sum.

Regards.

 

[Hui]

Frank

What is your definition of a week?

I'd suggest reading help on the Weeknum function

 

[bosco_yip]

Thanks to you and Hui.

My problem is almost solve with sumproduct except Quarter result of a year.

This is working
=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1})+(MONTH($A$2:$A$99999)={2})+(MONTH($A$2:$A$99999)={3}),$C$2:$C$99999)

But i want to make it simple. Like...
=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1,2,3}),$C$2:$C$99999)

What is going wrong?

Thanks and regards.

-Faruk Hosen

Instead of :

=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1})+(MONTH($A$2:$A$99999)={2})+(MONTH($A$2:$A$99999)={3}),$C$2:$C$99999)

Alternative formula :

=SUMPRODUCT(($A$2:$A$99999>=--("1-1-"&G$15))*($A$2:$A$99999<=--("31-3-"&G$15)),$C$2:$C$99999)

Or, the below shorter formula in using SUMIFS instead of SUMPRODUCT:

1] Q1 2015, cell F16 copy across

=SUMIFS($C:$C,$A:$A,">=01/01/"&F$15,$A:$A,"<=31/03/"&F$15)

1] Q2 2015, cell F17 copy across

=SUMIFS($C:$C,$A:$A,">=01/04/"&F$15,$A:$A,"<=30/06/"&F$15)

1] Q3 2015, cell F18 copy across

=SUMIFS($C:$C,$A:$A,">=01/07/"&F$15,$A:$A,"<=30/09/"&F$15)

1] Q4 2015, cell F19 copy across

=SUMIFS($C:$C,$A:$A,">=01/10/"&F$15,$A:$A,"<=31/12/"&F$15)

Regards
Bosco

Regards
Bosco

 

[Faruk Hosen]

Instead of :

=SUMPRODUCT(--(YEAR($A$2:$A$99999)=G$15)*(MONTH($A$2:$A$99999)={1})+(MONTH($A$2:$A$99999)={2})+(MONTH($A$2:$A$99999)={3}),$C$2:$C$99999)

Alternative formula :

=SUMPRODUCT(($A$2:$A$99999>=--("1-1-"&G$15))*($A$2:$A$99999<=--("31-3-"&G$15)),$C$2:$C$99999)

Or, the below shorter formula in using SUMIFS instead of SUMPRODUCT:

1] Q1 2015, cell F16 copy across

=SUMIFS($C:$C,$A:$A,">=01/01/"&F$15,$A:$A,"<=31/03/"&F$15)

1] Q2 2015, cell F17 copy across

=SUMIFS($C:$C,$A:$A,">=01/04/"&F$15,$A:$A,"<=30/06/"&F$15)

1] Q3 2015, cell F18 copy across

=SUMIFS($C:$C,$A:$A,">=01/07/"&F$15,$A:$A,"<=30/09/"&F$15)

1] Q4 2015, cell F19 copy across

=SUMIFS($C:$C,$A:$A,">=01/10/"&F$15,$A:$A,"<=31/12/"&F$15)

Regards
Bosco

Regards
Bosco

Thanks.
Now it is well understood.

Regards.

 

[Faruk Hosen]

Frank

What is your definition of a week?

I'd suggest reading help on the Weeknum function

OK. I will try.

Regards.