Formulas and counting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 19 May 2008 17:10 GMT

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?

Reply to this Message

T. Valko - 19 May 2008 17:40 GMT

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.

Signature

Biff
Microsoft Excel MVP

> II need to have a formula that will search through one column in a
> specific
[quoted text clipped - 12 lines]
> with hand counting through a filter and that is not productive. Any
> suggestions?

Reply to this Message

David - 19 May 2008 18:08 GMT

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?

> Try this...
>
[quoted text clipped - 27 lines]
> > with hand counting through a filter and that is not productive. Any
> > suggestions?

Reply to this Message

T. Valko - 19 May 2008 18:19 GMT

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))

Signature

Biff
Microsoft Excel MVP

>I tried this in excel 2003 and it dose not count the number of occurrences
>if
[quoted text clipped - 44 lines]
>> > with hand counting through a filter and that is not productive. Any
>> > suggestions?

Reply to this Message

David - 19 May 2008 20:28 GMT

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?

> 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
[quoted text clipped - 52 lines]
> >> > with hand counting through a filter and that is not productive. Any
> >> > suggestions?

Reply to this Message

David - 19 May 2008 20:37 GMT

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

> 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
[quoted text clipped - 59 lines]
> > >> > with hand counting through a filter and that is not productive. Any
> > >> > suggestions?

Reply to this Message

David - 19 May 2008 20:41 GMT

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?

Reply to this Message

David - 19 May 2008 20:58 GMT

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!

> Used your formula and it still doesn’t give me a 1 still evaluates to 0.
>
[quoted text clipped - 65 lines]
> > > >> > with hand counting through a filter and that is not productive. Any
> > > >> > suggestions?

Reply to this Message

T. Valko - 19 May 2008 21:27 GMT

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

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 92 lines]
>> > > >> > Any
>> > > >> > suggestions?

Reply to this Message

T. Valko - 19 May 2008 21:08 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Used your formula and it still doesn't give me a 1 still evaluates to 0.
>
[quoted text clipped - 82 lines]
>> > >> > Any
>> > >> > suggestions?

Reply to this Message