Sumproduct and Month gets #value

[Budrow]

I am getting #value or #NA

=SUMPRODUCT((MONTH(Activity!$C$3:$C$25)=7)*1,(Activity!$S:$S="Y")*1,(Activity!$G:$G,$A8,Activity!H:I)-(Activity!J:J,BS!$A$45,Activity!$K:$K),0)

In the first ( ) my column contains months or is BLANK and its is a date format
in the second ( ) my column contains Y or N or Blank
In the third ( ) I am simply multiplying amounts based on a condition
In the fourth ( ) I am simply multiplying amounts based on a condition

If I remove the Sumproduct and Month and us just an IF it works fine. I am using MONTH because I want to limit the condition to months.
=IF(Activity!$S:$S="Y",SUMIF(Activity!$G:$G,$A8,Activity!H:I)-SUMIF(Activity!J:J,BS!$A$45,Activity!$K:$K),0)


So if the month is in July AND the row has a Y then Sum values and subtract another Sum of values ELSE put a zero

 

[NARAYANK991]

Hi ,

The crucial point is whether the cells in the range Activity!$C$3:$C$25 are really blank or do they have a formula in them which populates them with null strings.

=MONTH(A1)

where A1 is a true blank will return 1.

If A1 contains a formula , such as :

=IF(B1 = "" , "" , B1)

then if B1 is blank , true or otherwise , A1 will have the null string in it , and the MONTH formula will now return the #VALUE! error value.

Narayan

 

[Budrow]

Hi - Thank you for responding...
The cells for the month all have an actual value. There is no formula.
(MONTH(Activity!$C$3:$C$25)
6/1/2016
7/1/2016
7/3/2016
etc

 

[xlstime]

can you please upload sample sheet with required output.

Hi - Thank you for responding...
The cells for the month all have an actual value. There is no formula.
(MONTH(Activity!$C$3:$C$25)
6/1/2016
7/1/2016
7/3/2016
etc

 

[Budrow]

See attached

 

[NARAYANK991]

Hi ,

Beyond row 25 , from row 26 onwards , the relevant cells have the #N/A error values in them. The SUMPRODUCT function which uses entire column references such as G:G , H:H will return the #N/A error value.

You need to restrict all references to row 25.

Narayan

 

[xlstime]

+ One more point.

For a better formula understanding and accurate result, i would strongly suggest you to always use parallel range in all criteria.

like you have used =SUMPRODUCT((MONTH(Activity!$C$3:$C$25)=7)*1,(Activity!$S:$S="Y")*1,(Activity!$G:$G,$A8,Activity!H:I)-(Activity!J:J,BS!$A$45,Activity!$K:$K),0)

Thanks.

Hi ,

Beyond row 25 , from row 26 onwards , the relevant cells have the #N/A error values in them. The SUMPRODUCT function which uses entire column references such as G:G , H:H will return the #N/A error value.

You need to restrict all references to row 25.

Narayan

 

[NARAYANK991]

Hi ,

Apart from the technical point mentioned earlier , I would like to know what exactly you want done ; only if this is clear , can we think of implementing the desired logic through an Excel formula.

In your existing formula , the segments highlighted in red are wrong and need to be revised.

=SUMPRODUCT((MONTH(Activity!$C$3:$C$25)=7)*1,(Activity!$S:$S="Y")*1,(Activity!$G:$G,$A8,Activity!H:I)-(Activity!J:J,BS!$A$45,Activity!$K:$K),0)

Column I in the Activity tab contains text.

Narayan

 

[Budrow]

Hi Thank you for input...
I have corrected the formula as such...

=SUMPRODUCT((MONTH(Activity!$C$3:$C$25)=7)*1,(Activity!$S3:$S25="Y")*1,(Activity!$G$3:$G$25,$A8,Activity!K$3:K$25)-(Activity!$M$3:$M$25,BS!$A$45,Activity!$K$3:$K$25),0)

Activity!$C$3:$C$25)=7 if the month in this column is 7 (july)
AND
Activity!$S3:$S25="Y" if the values in this column are Y then do the following else 0

This part of the equation works fine if I use an IF statement.
(Activity!$G$3:$G$25,$A8,Activity!K$3:K$25)-(Activity!$M$3:$M$25,BS!$A$45,Activity!$K$3:$K$25)

 

[Budrow]

Here is the new attachment