# Sumproduct: #Value! due to calulated blank cell

1. ## Sumproduct: #Value! due to calulated blank cell

I have the following formula to count the total of locations opened by month. If a center isn't going to open, the location's opening date will error and show a blank cell, but it's a calulated value. The sumproduct is =SUMPRODUCT((\$B\$172:\$B\$198<>"")*(MONTH(\$B\$172:\$B\$198)=MONTH(AW\$11))). Any help?

2. ## Re: Sumproduct: #Value! due to calulated blank cell

Try this version

=SUMPRODUCT((TEXT(\$B\$172:\$B\$198,"mmm;;")=TEXT(AW\$11,"mmm;;"))+0)

3. ## Re: Sumproduct: #Value! due to calulated blank cell

That's perfect! Thanks!!!

4. ## Re: Sumproduct: #Value! due to calulated blank cell

Here's another one...

Array entered**:

=SUM(IF(ISNUMBER(\$B\$172:\$B\$198),IF(MONTH(\$B\$172:\$B\$198)=MONTH(AW\$11),1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Let's go Pens!

There are currently 1 users browsing this thread. (0 members and 1 guests)