Do Not count blank cells

  • Thread starter Thread starter imjustme
  • Start date Start date
I

imjustme

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
 
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?
 
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????
 
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.)
 
Back
Top