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?