Formula HELP !!

[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

 

[somethinglikeant]

Firstly,

you need to use a counting formula such as COUNTIF
and you need a way of extracting the digits for the month from the date


If you have room to do this and your dates are stored in a column.
create a column with the following formula as the first entry
say column B, put this into B1

=MID(TEXT(A1,"dd/mm/yyyy"),4,2)

assuming A1 is your first date

copy this down however far you need it.

It i then easy to Count how many times each month appears

You will need to find room to put the following function into a cell
for each month

=COUNTIF(B:B,"04") for example.

will count how mny 04 in col B

Not the most direct way but may help you give you what you need

If anybody knows a quicker way using array formulas I'd like to learn
how.

Cheers

http://www.excel-ant.co.uk

 

[Guest]

This formula works OK but when no date entry is made the cell returns 01 so
the total is calculating a lot of 01 as Jan which is false information, any
ideas anyone on how to do this another way as not to create more columns as
the workbook is large enough and I dont really want to add more columns to it
as its already large.

Thanks


Mark