count if days of week and value

Hi.

I can't get this formula right. I have two columns of data. Column A is a date, Column B is a number. I want to a formula that will go through column A and check if the date is a Monday AND Column B is greater than 7 and return how many times this occurs.

This is what I have and it does not work.

=COUNTIFS(A:A,"ddd"="sun",B:B,">7")

What am I missing?

Try something along the lines of this:

=SUMPRODUCT((WEEKDAY(A2:A10)=2)*(B2:B10>7))

Changing the ranges to fit your data.

That works except for 7 saturday. I think excel counts empty cells..

it doesn't I suggest you attack a workbook, with the expected results

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.