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.

A	B	C
05/01/2015	Project 1	100
06/01/2015	Project 1	150
07/01/2015	Project 1	90
08/01/2015	Project 2	50
09/01/2015	Project 2	60
10/01/2015
11/01/2015

<tbody>
</tbody>

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

 

[RatExcel]

Try this. Use CTRL + SHIFT + ENTER when entering the formula

=INDEX(B16:B406,MATCH(MAX(SUMIFS(C16:C406,B16:B406,B16:B406,A16:A406,">="&O3,A16:A406,"<="&O3+6)),TRANSPOSE(SUMIFS(C16:C406,B16:B406,B16:B406,A16:A406,">="&O3,A16:A406,"<="&O3+6)),0))

 

[WhiteNoiseMaker]

Thank you so much RatExcel. You have made my day, it worked straightaway!

 

[WhiteNoiseMaker]

I've got a slight problem... When I drag/copy the formula down the the cells below, it still gives the same answer even though it should be looking at the data i've entered for a different week. This is based on the Monday dates held in the 'O' column.

I've made a change to lock the cells that I don't want to change, but left the 'O' column reference alone so it does change ...but still give the same answer.

Any ideas?

{=INDEX($B$16:$B$406,MATCH(MAX(SUMIFS($C$16:$C$406,$B$16:$B$406,$B$16:$B$406,$A$16:$A$406,">="&O3,$A$16:$A$406,"<="&O3+6)),TRANSPOSE(SUMIFS($C$16:$C$406,$B$16:$B$406,$B$16:$B$406,$A$16:$A$406,">="&O3,$A$16:$A$406,"<="&O3+6)),0))}

 

[barry houdini]

That sounds like you have Calculate set to "manual". Change that to "Automatic" under "Calculation Options" (at the right of the Formulas tab)

....or for a one-off re-calculation press F9 key

 

[RatExcel]

It must be what barry houdini pointed out. It works for me fine with calculation set to Automatic.

 

[WhiteNoiseMaker]

Oh actually thats not the problem. It works but I don't want it to base the answer on number of cases processed. It's just purely based on the number of times the job type appears. The numbers can be much higher for some work types that might only be done 1 day.

...I know this probably makes no sense whatsoever to count it this way, but thats what i've been asked for

 

[barry houdini]

If you are just trying to find the most common text value for the week try this version

=INDEX(B$16:B$406,MODE(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}))))<o3+7,match(b$16:b$406,b$16:b$406,0)*{1,1}))))


confirmed with CTRL+SHIFT+ENTER and copied down

If there are an equal number of two or more text values then the formula just returns the one that appears first for that week</o3+7,match(b$16:b$406,b$16:b$406,0)*{1,1}))))

 

[WhiteNoiseMaker]

Thanks for the help Barry. I've tried this formula this morning and it just returns '#N/A'... Sorry for being a pain

 

[RatExcel]

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})),"")))