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.
column C is equal to 16.
S
Sandy Mann
Try:
=SUMPRODUCT((D1
1000<>"")*(D11000=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
=SUMPRODUCT((D1
1000<>"")*(D11000=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
S
Sue
My 16 is coming from my data source as a text 16 I tried this
=SUMPRODUCT((D11000<>"")*(D11000=0)*(C1:C1000="16"))
but it did not find any and should have found 106
=SUMPRODUCT((D1
1000<>"")*(D11000=0)*(C1:C1000="16"))but it did not find any and should have found 106
B
BoniM
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((D11000=0)*(C1:C1000="16 "))
if your cell contains 16 followed by two spaces
=SUMPRODUCT(--(D21000=0),--(C2:C1000="16"))
if your cell contains only 16 in text format
=SUMPRODUCT(--(D21000=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!
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
1000=0)*(C1:C1000="16 "))if your cell contains 16 followed by two spaces
=SUMPRODUCT(--(D2
1000=0),--(C2:C1000="16"))if your cell contains only 16 in text format
=SUMPRODUCT(--(D2
1000=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!
B
BoniM
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((D11000=0)*(C1:C1000="16"))
and
=SUMPRODUCT(--(D21000=0),--(C2:C1000="16"))
will give you the same results.
trying to stick with the original post, but got interupted and forgot when I
came back...
So just FYI -
=SUMPRODUCT((D1
1000=0)*(C1:C1000="16"))and
=SUMPRODUCT(--(D2
1000=0),--(C2:C1000="16"))will give you the same results.
S
Sandy Mann
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
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
S
Sue
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
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