Excel Help.

[Davie_GLA]

Afternoon all.

i have a ratehr large spreadsheet that tracks how many messages were sent from a client on a month to month bass. I have a seperate sheet for each month and in those sheets i want to know how many messages were sent by day.

I have a column in there for date in dd/mm/yyyy format and i can use =COUNTIF(RANGE,CRITERIA) but it's a pain in the hole doing it seperately for each day.

Is there a way that i can search an entire colums for each date (01/10/2009 - 31/10/2009 for example) and spit out the total number of times it finds a each date?

So say i had a record of 20 messages for the 1st and 40 for the second and so on, can it be smart enough to know when to change?

I know someone os going to say macro - but i'm not that savvy.

Cheers,

D.

[hairykrishna]

I'm not entirely sure I'm understanding what you want but it sounds like you should have a play with the filters (data menu at the top).

[Liszt]

Subtotals, on change of date show COUNT

[MrsMiggins]

You could create a pivot table, but you'd have to refresh it to get an updated total as you made changes to the worksheet.

[Davie_GLA]

Hairy i have filters on, but that won't count it, Liz, can you expand on that?

Edited by Davie_GLA on Wednesday 7th October 14:55

[Mattt]

How do you want the data presented?

If you can't be arsed typing in the date into each formula, you could just write each date in a table then have the formula reference that e.g. Cell B1 =COUNTIF($C$1:$D$99,A1)

[Davie_GLA]

Matt, if i've picked you up right this should be my formula:

=CELL(J12=COUNTIF($C$10:$C$108,C15))

Where J12 is the field where i want to put the result, and C10 is the field that i want to use as a search string?

[Landlord]

If I've understood you correctly;

Have a sheet with a column of the dates you want;

	A	B
1	1/10/2009	=COUNTIF(RANGE, A1)
2	2/10/2009	=COUNTIF(RANGE, A2)

etc.

Oops - didn't read Mattt's answer earlier which is the same thing!

Edited by Landlord on Wednesday 7th October 15:30

[Mattt]

Davie_GLA said:

Matt, if i've picked you up right this should be my formula:

=CELL(J12=COUNTIF($C$10:$C$108,C15))

Where J12 is the field where i want to put the result, and C10 is the field that i want to use as a search string?

I presume you mean C15 is the search condition?

[Davie_GLA]

Sorry yes, C15 not C10.

[Davie_GLA]

OK, revised formula.

=CELL(J7=COUNTIF(C10:C108,I7))

If i've understood right - the above should search cells C10 to C108 for the contents of I7 and display the results in cell J7, non?

[Mrs Trackside]

Landlord said:

If I've understood you correctly;

Have a sheet with a column of the dates you want;

	A	B
1	1/10/2009	=COUNTIF(RANGE, A1)
2	2/10/2009	=COUNTIF(RANGE, A2)

etc.

Oops - didn't read Mattt's answer earlier which is the same thing!

Edited by Landlord on Wednesday 7th October 15:30

That's how I'd have done it too. Nice and simple

[Davie_GLA]

OK - sorted, i took Landlords advice.

Thanks!

Edited by Davie_GLA on Wednesday 7th October 16:47

[Mattt]

Davie_GLA said:

OK, revised formula.

=CELL(J7=COUNTIF(C10:C108,I7))

If i've understood right - the above should search cells C10 to C108 for the contents of I7 and display the results in cell J7, non?

Yes, but remember to use the $ sign if you are dragging it down.