A Query

[Pankaj Trivedi]

Hi,

I have been a user who have always done simple tasks like creating
table, formatting etc in Excel. But time requires that I need to get
used to using formula and other tools of excel therefore my query
might be so simple for the experienced users but I don't know how it
can be done. So excuse me for my ignorance:

I have a table that contains dates in one cell and time stamp in the
subequent cell and the same formate spanning over three months. I need
to exclude those cell having time stamp and count (month-wise) those
cells having date in them.

For example:

COLUMN A
31-Oct-07
10:49:27 PM
31-Oct-07
12:18:28 PM
30-Oct-07
12:32:27 PM

Can anyone help me?

Thanks,

Pankaj

 

[Pete_UK]

If those times are really just times (and not date-times), then you
could use this formula:

=COUNTIF(A:A,">1")

assuming the data is in column A - put the formula in a different
column. This will give you an overall count of the dates - not sure
what you mean by "count (month-wise)".

Hope this helps.

Pete

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--(TEXT($A$1:$A$100,"yymm")="0710"))

Or your could put the year and month in another cell, e.g. B1 and use
=SUMPRODUCT(--(TEXT($A$1:$A$100,"yymm")=$B$1))

 

[Pankaj Trivedi]

If those times are really just times (and not date-times), then you
could use this formula:

=COUNTIF(A:A,">1")

assuming the data is in column A - put the formula in a different
column. This will give you an overall count of the dates - not sure
what you mean by "count (month-wise)".

Hope this helps.

Pete

Hi Pete,

Thanks a lot. It is working.

Perhaps I failed to express myself properly when I said (month-wise).
In fact, my data was spanning over three month's time so I wanted to
have total at the end of every month. But anyways, I believe that may
have made the formula more complex and I know as of today that is not
my cup of tea. :=)


Pankaj

 

[Pankaj Trivedi]

Hi

One way
=SUMPRODUCT(--(TEXT($A$1:$A$100,"yymm")="0710"))

Or your could put the year and month in another cell, e.g. B1 and use
=SUMPRODUCT(--(TEXT($A$1:$A$100,"yymm")=$B$1))

Hi Roger,

Perhaps I am novice to understand the concept of array. I tried but
without success I am sure I must have created some sort of mess.

Anyways, I am thankful for your time and assistance.

Regards,

Pankaj

 

[Pete_UK]

Glad to hear it is working - thanks for feeding back.

Pete

 

[Roger Govier]

Hi

If you copied and used my formula, you would need to adjust the row number
to deal with the extent to which your data fills columns A
I just used a range of 100 rows as an example

You cannot give whole columns as arguments to the Sumproduct formulae.
You could use
=SUMPRODUCT(--(TEXT($A$1:$A$65535,"yymm")="0710"))
if your data was really going all the way down the sheet, but it is better
to restrict the formula to a smaller range that will encompass all of your
existing / likely future data range.

Testing for "0710" is testing for October 2007.
You mentioned in a reply to Pete, that you were looking for the results for
3 separate months. If you entered in
B1 '0708
B2 '0709
B3 '0710
Note the single quote in front of the entry to force the values to be text.
Then enter your formula in C1 as
=SUMPRODUCT(--(TEXT($A$1:$A$65535,"yymm")=$B1))
and copy down to C2:C3
you will have the results for each of the months.