Check your data.
Either your values aren't what you think they are or maybe you have hidden
rows???
Maybe you could add another column with a formula to help you find the
mismatches:
Insert a new column G and put this in G1:
=(f1="yes")&"--"&(d130000)&"--"&(d1<75000)
And drag down.
Now find one of the rows that you think should be counted and you should see
TRUE--TRUE--TRUE.
Your job will be to find the rows that aren't all true and determine why.
I'm betting either typing mistakes (extra characters in column F) or text that
looks like numbers in column D.
Dave wrote:
Hi
The formula works now for values between 30k and 75k as I have put these
values to record the correct amount of rows, however, the formula:
=SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not
work, I have written it as:
=SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and
this records a value of 256 (which is wrong) it sould be 324. If I change it
to "No" it records a value of 1,014 (again incorrect) it should 948.
"Jacob Skaria" wrote:
You must be using 2003. SUMPRODUCT should work with a defined range...Try
with D1:D1000
=SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000))
Refer this link for --
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
If this post helps click Yes
---------------
Jacob Skaria
"Dave" wrote:
Hi Jacob
Thanks for this.
The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2
hyphens represent~?
"Jacob Skaria" wrote:
Either use
=COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000")
OR
=SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000))
With another condition
=SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000))
=SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000))
--
If this post helps click Yes
---------------
Jacob Skaria
"Dave" wrote:
HI
I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO
USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30
AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP?
ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN
WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE
TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH
APPRECIATED
--
Dave Peterson