Hi all!!

I have been trying to figure out how to NOT have excel count blank
cells when it is counting my information that my formulas are telling
it to count. This is my current formula (array):

=SUMPRODUCT(--(oc!$C$1:$C$1000=435),--(oc!$H$1:$H$2000="approved"),--(oc!$D$1:$D$1000="telemedicine")

I have to put a space in each blank cell (which could be a bunch)

Thanks

Dee


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=480665

Why would it count blank cells?


--

Regards,

Peo Sjoblom

"imjustme" wrote in
message ...

Hi all!!

I have been trying to figure out how to NOT have excel count blank
cells when it is counting my information that my formulas are telling
it to count. This is my current formula (array):


=SUMPRODUCT(--(oc!$C$1:$C$1000=435),--(oc!$H$1:$H$2000="approved"),--(oc!$D$
1:$D$1000="telemedicine")

I have to put a space in each blank cell (which could be a bunch)

Thanks

Dee


--
imjustme
------------------------------------------------------------------------
imjustme's Profile:
http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=480665

I'm kind of amazed it worked at all.

First, this shouldn't be array entered (ctrl-shift-enter) if that's what you
meant. Just hitting enter will be sufficient.

Second, you're missing a final close parenthesis at the end.

And lastly, don't you want all the ranges to be 1:1000 or 1:2000?



imjustme wrote:

Hi all!!

I have been trying to figure out how to NOT have excel count blank
cells when it is counting my information that my formulas are telling
it to count. This is my current formula (array):

=SUMPRODUCT(--(oc!$C$1:$C$1000=435),--(oc!$H$1:$H$2000="approved"),--(oc!$D$1:$D$1000="telemedicine")

I have to put a space in each blank cell (which could be a bunch)

Thanks

Dee

--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=480665

--

Dave Peterson

I do not know why it is counting the blank cells. There used to be
information in them and then I deleted it out. It will not do it for
every month just 3 out of the 12.

=SUMPRODUCT(--(ma!$C$1:$C$1000=690),--(ma!$H$1:$H$1000="approved"),--(ma!$K$1:$K$1000="outpatient")

The reason I was missing a ")" is because when you do C+S+E it will add
it when it adds the }.

=SUMPRODUCT(--(ma!$C$1:$C$1000=690),--(ma!$H$1:$H$1000="approved"),--(ma!$K$1:$K$1000="outpatient"))
This is the formula I should use????


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=480665

Again, this doesn't need to be entered as an array formula.

And yep, excel will offer to correct the missing parenthesis.

Any chance you have calculation set to manual.
Tools|options|calculation tab is where you would check.

I don't see any reason for it to count blank cells.

if you use 3 separate cells:
=SUMPRODUCT(--(ma!$C$1:$C$1000=690))
=SUMPRODUCT(--(ma!$H$1:$H$1000="approved"))
=SUMPRODUCT(--(ma!$K$1:$K$1000="outpatient"))

Do each of those count correctly? (Each of those answers won't be the same as
the long formula--well, unless by chance.)


imjustme wrote:

I do not know why it is counting the blank cells. There used to be
information in them and then I deleted it out. It will not do it for
every month just 3 out of the 12.

=SUMPRODUCT(--(ma!$C$1:$C$1000=690),--(ma!$H$1:$H$1000="approved"),--(ma!$K$1:$K$1000="outpatient")

The reason I was missing a ")" is because when you do C+S+E it will add
it when it adds the }.

=SUMPRODUCT(--(ma!$C$1:$C$1000=690),--(ma!$H$1:$H$1000="approved"),--(ma!$K$1:$K$1000="outpatient"))
This is the formula I should use????

--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=480665

--

Dave Peterson