X=1 Help!!!

[prhrmk]

I have a spreadsheet where I have a cell (P1) NAMED "Free". In the
following range ($P$2:$P$65536) - I will be using X, or x to indicate
it's free of charge. I want this to be equal to 1. The formula that I
am using is ----- =SUMPRODUCT('[MyRecord Log
2006.xls]Akron-06'!$P$2:$P$65536)--('[MyRecord Log
2006.xls]Akron-06'!$N$2:$N$65536>=--"2006-01-01")--('[MyRecord Log
2006.xls]Akron-06'!$N$2:$N$65536<=--"2006-01-06")

The formual works but it is only counting the X,x as one regardless if
I have many checked in that date range.

Please help and I hope this made sense???

Thanks,
Paul

 

[Bob Phillips]

Try

=SUMPRODUCT('[MyRecord Log 2006.xls]Akron-06'!$P$2:$P$65536),
--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536>=--"2006-01-01"),
--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536<=--"2006-01-06")


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[prhrmk]

BOb - thanks for the input but you just gave me the formula I am
currently using. I need help with the x=1 part of the formula so that
when excel sees and x in that cell it will count it as one. Does that
make sense?

Thanks,
Paul

 

[Bob Phillips]

Paul,

I did change it, I added some commas. Try it and see if it works.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Just noticed it needs extra brackets as well

=SUMPRODUCT(('[MyRecord Log 2006.xls]Akron-06'!$P$2:$P$65536),
--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536>=--"2006-01-01"),
--('[MyRecord Log 2006.xls]Akron-06'!$N$2:$N$65536<=--"2006-01-06"))

--

HTH

RP
(remove nothere from the email address if mailing direct)