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.



OK, I think I have made a formula that should suit your needs. It is displayed below:

=IF((NOW())-7>=A2,"", IF(A2<=(NOW()), (COUNTIF(B2:F2, "M")), ""))

What this formula does is compare cell A2 to the current date. If cell A2's date isn't week commencing, it will display nothing. If cell A2's date IS week commencing, it will display the total of all the M's in that row.

A working example can be seen below:

For the formula to work on a whole column, it will need minor changes on each row. Luckily, Excel can do this for you automatically. All you have to do is drag the bottom-right corner of the formula's cell downwards (more information about this can be found HERE).

Please let me know if it is not working as you wanted it.

  • Many thanks for your response! I think my description of the formula's requirements is a bit ambiguous, but the image you posted will make it much easier to explain. So from your image; I need to lookup the row with the date in column A that corresponds to today's week commencing date. Today's week commencing date could be calculated in G1 (I already have a formula to obtain this), then the formula in H2 could reference G1 to make it simpler. When this row is looked up, I then need to count the number of cells that contain "M". Hope this explains it a bit better. Cheers! – Dave Feb 21 '14 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.