Count most common text value in a specified date range.

[WhiteNoiseMaker]

My first post and I’m desperately in need of help!


I have a list of dates running from 01/01/2015 to 31/12/2015 in column A. This is followed by the task performed that day, and how much has been processed e.g.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]05/01/2015[/TD]
[TD]Project 1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]06/01/2015[/TD]
[TD]Project 1[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]07/01/2015[/TD]
[TD]Project 1[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]08/01/2015[/TD]
[TD]Project 2[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]09/01/2015[/TD]
[TD]Project 2[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]10/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I need to be able to count the most common task performed for each week. In the example above the answer I would expect is Project 1, as the majority of the week was spent working on this project.


The example I’ve found below works but I need to specify the date range I want to check, which is too time consuming.


=INDEX(B16:B22,MATCH(MAX(COUNTIF(B16:B22,B16:B22)),COUNTIF(B16:B22,B16:B22),0))


I’m trying to combine the formula above with a formula i have working for counting the totals for each week but I just can’t get it to work.


=SUMIFS($C$16:$C$406,$A$16:$A$406,">="&$O3,$A$16:$A$406,"<="&$O3+6) (O3 contains my list of Monday dates)

Any help would be greatly appreciated

 

[WhiteNoiseMaker]

Try this - use CTRL + SHIFT + ENTER when entering the formula

=INDEX(B$16:B$406,MODE(IFERROR(IF(A$16:A$406>=O3,IF(A$16:A$406<o3+7,match(b$16:b$406,b$16:b$406,0)*{1,1})),"")))[ code][="" quote]


What is the missing bit off the end of this formula? I tried adding </o3+7,match(b$16:b$406,b$16:b$406,0)*{1,1})),"")))[>[FONT=Lucida Grande]<[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]O3[/FONT][/COLOR][FONT=Lucida Grande]+7,0) but that didn't work.[/FONT]

 

[RatExcel]

Ok, try to download this, I pasted the formula into notepad:

https://app.box.com/s/wxd5dp88s1c2ar06iprnq7ip032sr1pu

 

[WhiteNoiseMaker]

Ok, try to download this, I pasted the formula into notepad:

https://app.box.com/s/wxd5dp88s1c2ar06iprnq7ip032sr1pu

Thats works, thanks again for your help.

 

[barry houdini]

Sometimes > or < signs are interpreted as HTML tags and the formula gets cut off at that point, you can leave a space at the open end and it should display correctly, i.e.

=INDEX(B$16:B$406,MODE(IFERROR(IF(A$16:A$406>=O3,IF(A$16:A$406< O3+7,MATCH(B$16:B$406,B$16:B$406,0)*{1,1})),"")))