Sumproduct stopped working

[JTL9161]

I was using a sumproduct formula (below) to find numbers in column W to give me an average by month. It was working fine but I added a formula to column B to add the current day when a number is added to column W and now the sumproduct gives me a #value ! error. I tried removing the date formula from column b but I still am getting the error. What changed? Help!



=SUMPRODUCT(--(B282:B303<>""),--(MONTH(B282:B303)=4),W282:W303/SUMPRODUCT(--(B282:B303<>""),--(MONTH(B282:B303)=4)))

 

[Domenic]

You've probably already figured it out by now, but just in case you're still having problems, it looks like you've omitted a bracket in the first SUMPRODUCT, and added an extra one in the second. Try...

=SUMPRODUCT(--(B282:B303<>""),--(MONTH(B282:B303)=4),W282:W303)/SUMPRODUCT(--(B282:B303<>""),--(MONTH(B282:B303)=4))

Hope this helps!

 

[JTL9161]

Thanks but not it. I checked the formula with a similar spreadsheet with the same formula and it works on that one. When I copy it over though I STILL get the #value ! error. I am totally lost on this one.

 

[Domenic]

Can you post the formula you're using for Column B?

 

[JTL9161]

It just a simple IF statement that when data is entered in column W then take the date in the above line in column B and add 7 days.

=IF(W288<>"",B287+7,"")

I just used cells at the bottom of the spreadsheet to have the SUMPRODUCT point to and it works fine like it did before. So the IF formula above has something to do with it.

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(ISNUMBER(B282:B303),IF(MONTH(B282:B303)=4,W282:W303)))/SUM(IF(ISNUMBER(B282:B303),IF(MONTH(B282:B303)=4,1)))

Hope this helps!

 

[JTL9161]

Thanks. I'll try it and let you know.

 

[Domenic]

You can also use the AVERAGE function, and include the conditional statements...

=AVERAGE(IF(ISNUMBER(B282:B303),IF(MONTH(B282:B303)=4,W282:W303)))

...confirmed with CONTROL+SHIFT+ENTER.