Formula for cells

G

Guest

I have the following query, I need a formula to recognise certain Data in a
range of cells for a total to be displayed in connection of how many times
this data is entered.

e.g. I have a date 02/04/06, 03/09/06, I need a formula to recognise the
middle numbers only, 04 and 09 and return a total of howmany times this is
entered into a range of cells, can anyone give me some advice on how to do
this?

Thank a lot


Mark
 
G

Guest

In the worksheet you can use the COUNTIF() function; in VBA:

Sub countit()
Dim r As Range
iamthecount = 0
For Each r In Selection
d = r.Value
If IsDate(d) Then
m = Month(d)
If m = 4 Then
iamthecount = iamthecount + 1
End If
End If
Next
MsgBox (iamthecount)
End Sub

Just select the block of cells and run the macro. It counts Aprils. I am
assuming that the middle numbers are the month (European convention). If you
want the middle numbers to be the day, then substitute
m = Month(d)
with
m = Day(d)
 
G

Guest

Tries COUNTIF but does not return proper data as only recognises certain text
in " " cant get it to recognise numbers in the middle e.g. 12/"08"/06, I'm
not farmiliar with VBA wouldn't know where to start with this code.

Mark
 
G

Guest

Tried this sumproduct and cant get it to return data getting error #name in
cell. I need it to recognise center of date e.g. 12/"09"/06. would recognise
09

Any ideas?

Mark
 
G

Guest

Still not really what I need, I need it to add all months entries, Jan
through to Dec and total all in another sheet with cells named Jan to Dec and
have the totals as per their month.

Does this make sense?

Mark
 
D

Don Guillett

Your ORIGINAL request said that you wanted to know how many entries there
were for the month of Mar and the month of Sep. Assuming a properly
formatted date, what I gave will count these entries for column H.
 
G

Guest

Mark has posted this two or three times and can't seem to explain what he
really wants. I think it might be that he has a column with various dates
and he wants to count how many times each month of the year occurs in that
column and put the twelve totals on another sheet. Is this your objective
Mark?
 
G

Guest

Yes this is my objective, it seems to be quite complicated as countif only
recognises text in " " I need a formula to recognise only the month parte.g.
01,02,03,04,05,06,07,08,09,10,11,12 and then total the enries onto another
sheet as per the respective month.

Many thanks for your help.


Mark
 
G

Guest

Got it working Don but I have to have other Data in the same column down such
as POS and 45M this is picking up that data also and returnig a value error,
is there any way around this??


Mark
 
G

Guest

Just also noticed that when there is no entry in the calls it returns data if
the cell is empty and also if I enter 01/05/06 and have the formula to Month
01 it counts the dates starting 01.

Sorry to be a pain, any ideas??

Mark
 
B

Bob Phillips

=SUMPRODUCT(--(H2:H22<>""),--(ISNUMBER(MATCH(MONTH(H2:H22),{4,9},0))))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Great stuff Bob, Thanks a million.


Mark

Bob Phillips said:
=SUMPRODUCT(--(H2:H22<>""),--(ISNUMBER(MATCH(MONTH(H2:H22),{4,9},0))))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Hi Bob, Just put this formula into practice and does work to the date but I
also have Incident numbers input into the same column such as 1719665 and
this formula is also recognising this number and adding totals which in turn
is resulting false info as I only require the date format to be totalled, I
have tried selecting just the cells which will have the date in them in the
column but it is returning the formula is too long as there is a lot of
cells, do you have any other ideas how to combat this problem. Your help is
much appreciated.

Thanks


Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top