II need to have a formula that will search through one column in a specific
range (check Sheet1!C:C for a date range) if it is within that range then
check Sheet1!E:E for a specific value if it is that value increment this cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can think of
to get it to count as if it were a COUNTIF function operating with in a date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am left
with hand counting through a filter and that is not productive. Any
suggestions?

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range then
check Sheet1!E:E for a specific value if it is that value increment this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

I tried this in excel 2003 and it dose not count the number of occurrences if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range then
check Sheet1!E:E for a specific value if it is that value increment this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

The formula you just posted has a logic error. You're testing the same range
for 3 conditions and the range will *never* meet all 3 conditions so it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range
then
check Sheet1!E:E for a specific value if it is that value increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can
think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

This is the exact formula you gave me. Just the range is different because it
is within my search area. What should I do different? My range starts at 17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a limitation,
or is the example flawed in some way. I really hoped that this would work
because as you can see the range is abundant and being dynamic it has grown
since our last example. I cannot see the logical error what am I missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the same range
for 3 conditions and the range will *never* meet all 3 conditions so it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range
then
check Sheet1!E:E for a specific value if it is that value increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can
think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

I see it I will try it I had a brain cramp!!

"David" wrote:

This is the exact formula you gave me. Just the range is different because it
is within my search area. What should I do different? My range starts at 17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a limitation,
or is the example flawed in some way. I really hoped that this would work
because as you can see the range is abundant and being dynamic it has grown
since our last example. I cannot see the logical error what am I missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the same range
for 3 conditions and the range will *never* meet all 3 conditions so it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range
then
check Sheet1!E:E for a specific value if it is that value increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can
think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

Used your formula and it still doesnt give me a 1 still evaluates to 0.

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

Is there an error in this? Or something else I should try?


"David" wrote:

This is the exact formula you gave me. Just the range is different because it
is within my search area. What should I do different? My range starts at 17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a limitation,
or is the example flawed in some way. I really hoped that this would work
because as you can see the range is abundant and being dynamic it has grown
since our last example. I cannot see the logical error what am I missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the same range
for 3 conditions and the range will *never* meet all 3 conditions so it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range
then
check Sheet1!E:E for a specific value if it is that value increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can
think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

ValKo I am sorry you have to deal with such idiots in this forum. First I
neglected to mention the number is specific to an error code and must be
stored as text so I must surround that number in quotes €œ0106.1€� your formula
was exact as described and I am glad you were there to help the excel
challenged.

Thanks for your help!


"David" wrote:

Used your formula and it still doesnt give me a 1 still evaluates to 0.

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

Is there an error in this? Or something else I should try?


"David" wrote:

This is the exact formula you gave me. Just the range is different because it
is within my search area. What should I do different? My range starts at 17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a limitation,
or is the example flawed in some way. I really hoped that this would work
because as you can see the range is abundant and being dynamic it has grown
since our last example. I cannot see the logical error what am I missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the same range
for 3 conditions and the range will *never* meet all 3 conditions so it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that range
then
check Sheet1!E:E for a specific value if it is that value increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I can
think
of
to get it to count as if it were a COUNTIF function operating with in a
date
range. This is a shared data base and the data is dynamic so it is
impossible to break it into separate sheets for counting. As it is I am
left
with hand counting through a filter and that is not productive. Any
suggestions?

The formula is correct so that means there's a problem with your data.

Are the dates true Excel dates? True Excel dates are really just numbers
formatted to look like a date. For example:

A1 = 5/19/2008

As a true Excel date A1 looks like a date but its true underlying value is
39587. You can test the cell to make sure it is in fact a number formatted
to date:

=ISNUMBER(A1)

This will return TRUE if A1 is a true Excel date.

Other causes could be leading/trailing spaces in the cells. Leading trailing
spaces will cause the cell entry to be evaluated as TEXT and not as a
numeric date.

Are you sure the values in column H are numeric numbers and not TEXT
numbers? You can test these using the same method: =ISNUMBER(H1)



--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Used your formula and it still doesn't give me a 1 still evaluates to 0.

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

Is there an error in this? Or something else I should try?


"David" wrote:

This is the exact formula you gave me. Just the range is different
because it
is within my search area. What should I do different? My range starts at
17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a
limitation,
or is the example flawed in some way. I really hoped that this would
work
because as you can see the range is abundant and being dynamic it has
grown
since our last example. I cannot see the logical error what am I
missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the same
range
for 3 conditions and the range will *never* meet all 3 conditions so it
will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of
occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand
counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in a
specific
range (check Sheet1!C:C for a date range) if it is within that
range
then
check Sheet1!E:E for a specific value if it is that value
increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I
can
think
of
to get it to count as if it were a COUNTIF function operating with
in a
date
range. This is a shared data base and the data is dynamic so it
is
impossible to break it into separate sheets for counting. As it is
I am
left
with hand counting through a filter and that is not productive.
Any
suggestions?

Ok, glad you got it worked out. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
ValKo I am sorry you have to deal with such idiots in this forum. First I
neglected to mention the number is specific to an error code and must be
stored as text so I must surround that number in quotes "0106.1" your
formula
was exact as described and I am glad you were there to help the excel
challenged.

Thanks for your help!


"David" wrote:

Used your formula and it still doesn't give me a 1 still evaluates to 0.

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

Is there an error in this? Or something else I should try?


"David" wrote:

This is the exact formula you gave me. Just the range is different
because it
is within my search area. What should I do different? My range starts
at 17
yours starts at 1, mine ends at 9676 yours stops at 100 is this a
limitation,
or is the example flawed in some way. I really hoped that this would
work
because as you can see the range is abundant and being dynamic it has
grown
since our last example. I cannot see the logical error what am I
missing?

"T. Valko" wrote:

The formula you just posted has a logic error. You're testing the
same range
for 3 conditions and the range will *never* meet all 3 conditions so
it will
*always* return 0.

Shouldn't it be:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!H17:H9676=106.1))

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I tried this in excel 2003 and it dose not count the number of
occurrences
if
that specific value in the cell indicated. Is there another way?
I used:

=SUMPRODUCT(--(Sheet1!E17:E9676=DATE(2008,2,1)),--(Sheet1!E17:E9676<=DATE(2008,2,29)),--(Sheet1!E17:E9676=106.1))

I expect to see 1 returned as the value as I have already hand
counted
this
outcome I get zero.

am I supposed to nest this in another formula?

"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Sheet1!E1:E100=DATE(2008,2,1)),--(Sheet1!E1:E100<=DATE(2008,2,29)),--(Sheet1!H1:H100=106.1))

Better to use cells to hold the criteria:

A1 = 2/1/2008
B1 = 2/29/2008
C1 = 106.1

=SUMPRODUCT(--(Sheet1!E1:E100=A1),--(Sheet1!E1:E100<=B1),--(Sheet1!H1:H100=C1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.


--
Biff
Microsoft Excel MVP


"David" wrote in message
...
II need to have a formula that will search through one column in
a
specific
range (check Sheet1!C:C for a date range) if it is within that
range
then
check Sheet1!E:E for a specific value if it is that value
increment
this
cell
by one. Is that possible in excel? I tried and if statement like
=IF((AND(Sheet1!E:E=1-FEB-2008,Sheet1!E:E<=29-FEB-2008)),COUNTIF(Sheet1!H:H,106.1),
) this evaluates to #NAME? I have tried it in every variation I
can
think
of
to get it to count as if it were a COUNTIF function operating
with in a
date
range. This is a shared data base and the data is dynamic so it
is
impossible to break it into separate sheets for counting. As it
is I am
left
with hand counting through a filter and that is not productive.
Any
suggestions?