Counting depending on another column

[Sungibungi]

I am trying to count the number fo weekdays (Sun-Thu) and weekends (Fri-Sat).
I have two columns. A --> Date, B --> Day of Week (DOW). They are not linked
in any way.

Countif would work easily if the sheet was setup normally. However, the DOW
is static (Sun-Sat 5 times + Sun, Mon, Tue to cover every possible monthly
combination) and the Dates move depending on the month and when it starts. So
there are always DOWs that are blank in column A.

So how do I count the true number of weekdays and weekends? Feels like it
should be something like IF column A has values, THEN countif column B is
Fri/Sat or something like that.


Thanks for all your help in advance.

 

[Luke M]

To count weekdays:
=SUMPRODUCT(--(ISNUMBER(A2:A10)),--(ISNUMBER(MATCH(B2:B10,{"Sun","Mon","Tue","Wed","Thu"},0))))

Weekends:
=SUMPRODUCT(--(ISNUMBER(A2:A10)),--(ISNUMBER(MATCH(B2:B10,{"Fri","Sat"},0))))

Alternatively, can you make column B:
=IF(ISNUMBER(A2),TEXT(A2,"ddd"),"")

Then you could just do a simple COUNTIF

 

[Sungibungi]

Excellent. That formula works out real well.

If I may ask a question, what is the -- in front of (isnumber)? What does
that do to the formula?

Thanks so much for your help.


Sung

 

[Chip Pearson]

If I may ask a question, what is the -- in front of (isnumber)?

It converts the Boolean TRUE or FALSE values to their numeric
equivalents, 1 and 0, so they can be used in arithmetic by the
SUMPRODUCT function. The double negation is in effect multiplying the
value by -1 twice, and since -1 * -1 = 1, the value doesn't change.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Vijay]

One more alternate with sum and countif

for week days
=SUM(COUNTIFS(A2:A39,"<>"&"",B2:B39,{"sun","mon","tue","wed","thu"}))

For week ends =SUM(COUNTIFS(A2:A39,"<>"&"",B2:B39,{"fri","sat"}))

Vijay