Counting depending on another column
 

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.

Counting depending on another column
 

To count weekdays:
=SUMPRODUCT(--(ISNUMBER(A2:A10)),--(ISNUMBER(MATCH(B2:B10,{"Sun","Mon","Tue","Wed","T hu"},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
--
Best Regards,

Luke M
"Sungibungi" wrote in message
...
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.

Counting depending on another column
 

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


"Luke M" wrote:

To count weekdays:
=SUMPRODUCT(--(ISNUMBER(A2:A10)),--(ISNUMBER(MATCH(B2:B10,{"Sun","Mon","Tue","Wed","T hu"},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
--
Best Regards,

Luke M
"Sungibungi" wrote in message
...
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.


.

Counting depending on another column
 

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



On Fri, 5 Mar 2010 11:51:01 -0800, Sungibungi
wrote:

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


"Luke M" wrote:

To count weekdays:
=SUMPRODUCT(--(ISNUMBER(A2:A10)),--(ISNUMBER(MATCH(B2:B10,{"Sun","Mon","Tue","Wed","T hu"},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
--
Best Regards,

Luke M
"Sungibungi" wrote in message
...
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.


.

Counting depending on another column
 

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

"Sungibungi" wrote:

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.