Formulas and counting

[David]

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?

 

[T. Valko]

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.

 

[T. Valko]

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

 

[David]

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?

 

[David]

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

 

[David]

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]

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!

 

[T. Valko]

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)

 

[T. Valko]

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