Most common text string between dates

[mat*46*]

Hi, hoping one of you can help with my problem.

Basically I have a spreadsheet that I record every cycling trip I make. All of the data is on one sheet. In column A i have the date of the ride. In column J i have the name of the bike I used.

There is a seperate sheet called summary that gives me monthly totals.I.E. Miles, average speed etc.

Next to each month I would like excel to calculate the the bike that I used each month.

This formula tells me the bike I have used the most, but how do I get it to factor in the date and tell me which bike was used most in jan, which in feb etc.

=INDEX(Data!J3:J351,MODE(MATCH(Data!J3:J351,Data!J3:J351,0)))

Thanks in advance.

 

[mat*46*]

bmp

 

[Haldoug]

Have you tried pivot tables?

 

[AlphaFrog]

Try something like this...

=INDEX(Data!J3:J351,MODE(IF([COLOR="Red"]TEXT(Data!A3:A351,"mmm")="Jan"[/COLOR],MATCH(Data!J3:J351,Data!J3:J351,0))))

This is an array formula. When you enter in this formula, use Ctrl+Shift+Enter. Excel will automatically surround the formula with {braces}.

There are different functions you could use to define the dates by month.

 

[mat*46*]

Hi. Thanks for your reply. It's better but not quite right.

One thing I didn't make clear was that the dates on the data sheet a specific dates when I've been on my bike. Example 01/02/2011. The summary sheet is just the name of the month. June for example.

The summary sheet is split for each year and has been going since 2009. I need the formula to say that in june 2009 I used this bike the most, in december 2010 it was this bike etc.

Pretty anal I know but if anyone can help it would be appreciated.

Thanks

 

[AlphaFrog]

This will find the most used bike for "Jan 2009". You could substitute the text "Jan 2009" for a cell reference which contains "Jan 2009"

=INDEX(Data!J3:J351,MODE(IF([COLOR="Red"]TEXT(Data!A3:A351,"mmm yyyy")="Jan 2009"[/COLOR],MATCH(Data!J3:J351,Data!J3:J351,0))))

This is an array formula. When you enter in this formula, use Ctrl+Shift+Enter. Excel will automatically surround the formula with {braces}.

 

[mat*46*]

Excellent and thank you very much.