I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date.

I need a formula that will count the number of cells that contain "M" in last week's row. For example, I need to count the number of cells that contain "M" in row x if row x contains today's week commencing date in column B. As the week commencing progresses, the formula needs to change which row it counts "M" on.

I think the formula needs to look up last week's week commencing date from column B, then count all of the times the letter M occurs in any cells in the row of that week commencing date, but I've been trying (and failing) for some time to get this to work.

Any help would be greatly appreciated!

Apologies for the lack of image, as I'm new to this I don't have permission to upload one yet.

Cheers