Countif

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

Need help with a fomula to count how many times Column D has a value of 0 if
column C is equal to 16.
 
Try:

=SUMPRODUCT((D1:D1000<>"")*(D1:D1000=0)*(C1:C1000=16))

Adjust the rage to suit but not that you can't use whole columns.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
My 16 is coming from my data source as a text 16 I tried this
=SUMPRODUCT((D1:D1000<>"")*(D1:D1000=0)*(C1:C1000="16"))
but it did not find any and should have found 106
 
Perhaps your 16 includes spaces. If they are all the same, you could include
the spaces in sumproduct, or you could use the trim function to remove the
spaces, or use paste special to multiply column c by 1 to convert to a number.

=SUMPRODUCT((D1:D1000=0)*(C1:C1000="16 "))
if your cell contains 16 followed by two spaces

=SUMPRODUCT(--(D2:D1000=0),--(C2:C1000="16"))
if your cell contains only 16 in text format

=SUMPRODUCT(--(D2:D1000=0),--(C2:C1000=16))
if you convert 16 to number

You would want to do the same sort of adjustments if it's possible the 0 is
not an actual zero as well...
good luck!
 
I'm sorry, I did not mean to switch formats on you half way thru... was
trying to stick with the original post, but got interupted and forgot when I
came back...

So just FYI -
=SUMPRODUCT((D1:D1000=0)*(C1:C1000="16"))
and
=SUMPRODUCT(--(D2:D1000=0),--(C2:C1000="16"))
will give you the same results.
 
Just to add to BoniM's post, it could also be that you have a non-breaking
space, character 160 which TRIM() will not remove. If you are stillhaving
trouble post back.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks so much, that works, found I also had a problem in formula prior to
this where I will telling it to return a value of 0 if it did not find a
match and my 0 was not formatted properly. Thanks again
 
Back
Top