Column A = dates, Column C = text data (either blank, N, P, or C). I would like to count the number of occurrences where column A is a Saturday or Sunday *AND* when column C is not blank.

Col A Col C
1/10/15 P


1/31/15 C
3/1/15
4/3/15 N
4/4/15 P

In the example above, the answer would be 2 as row 1 and row 4 would meet both criteria

Try Adapt the ranges to your need but they MUST have the same length

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

There's probably a better way but at first blush this is how I would do it:

Add another column with the formula =WEEKDAY(A1,2), copy it down the sheet. Then your formula is:

or

Yep...I knew there was a better way. :-)

Row 5 too, right?

This worked - thanks! Not that I am asking anyone to look at this further, but I am still frustrated that the WEEKDAY function behaves so badly within the COUNTIFS and SUMPRODUCT formulas. The initial suggestion using SUMPRODUCT did not give the correct answer, I had been trying many different approaches to get SUMPRODUCT or COUNTIFS to work using WEEKDAYS as criteria.

You mean Peco's formula? I tried it and it worked fine. The answer for the data given is 3.

Ah! Peco's did work, of course! I needed to modify the arguments to "WEEKDAY(A2:A6,2)" for it to work correctly in the actual spreadsheet where I want to use this. I can't explain why, but there is something about SUMPRODUCT that confuses my. I think it has to do with using "*" in the equation to "multiply" things when you are actually not performing any multiplication. In any event, THANKS to everyone for such fast responses. I have loved Excel since it first came out, and it seems there is very little it can't do

Actually, it does do multiplication. It sums the multiplications done between the arrays specified.

But that raises something that I am confused about and maybe Peco can explain it. As explained by Microsoft, the arrays are supposed to be separated by commas. Sometimes I have to separate them with asterisks, as you say, or it doesn't work. Sometimes I have to use commas. Sometimes I have to use a comma here and an asterisk there.

Anyhoo, there seems to be an explanation here but I will have to read it later. Gotta hit the dusty trail.

http://www.mrexcel.com/forum/excel-q...-asterisk.html

More info about SUMPRODUCT can be found at Bob Philips' site

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post