In excel how to count entries respect to each week

Question

In excel how to highlight all entries for the week, if the count of entries within that week exceeds more than a certain value.

For ex: I have set of orders that get allocated weekly basis, but I need an indication if the number of orders allocated exceeds a certain value

Answer 1

Break this down logically.

First, you have to figure out which week an entry belongs to. You can use the WEEKNUM() function for that. The second parameter determines all kinds of variations on what day a week starts and what is considered week 1. Put the WeekNum() formula into a column (Screenshot column C)

=WEEKNUM(B2)

Now, in your data table, count the data by the week number of the date. Put that into a new column (screenshot column D)

=COUNTIF($C$2:$C$13,C2)

Next, you need to have an allocation reference somewhere, with a date (which can be translated into a week number) and the allocation for that week. That will be a list with one entry per week and the allocation number next to the week date. This table can then be used in a lookup. In the screenshot this table is in G3 to I6.

Now you can look up the allocation for each entry in the original data table. Using the new Xlookup:

=XLOOKUP(C2,$H$3:$H$6,$I$3:$I$6)

Or, if you don't have Office 365, go the Vlookup way:

=vlookup(c2,$H$3:$I$6,2,false)

You can now compare if the count and the looked up allocation are over. Do that in a different column, or roll it into one (screenshot column E)

=COUNTIF($C$2:$C$13,C2)>XLOOKUP(C2,$H$3:$H$6,$I$3:$I$6)

This formula returns TRUE or FALSE and the output can be used for Conditional formatting based on a cell value.

If you are brave enough, you can use the formula in a Conditional Formatting rule directly, but you need to take care with the cell referencing. In the screenshot, I selected cells A2 to B13 and applied this formula

=COUNTIF($C$2:$C$13,$C2)>XLOOKUP($C2,$H$3:$H$6,$I$3:$I$6)

Note the cell reference to $C2, which anchors the reference to column C but uses a relative reference for the row number.