I have rows of data across the spreadsheet and a total column at the
left. I need to count instances of strings ("A", "X", etc) but the
columns are not contiguous. I want to count weekdays only, so I need to
count many blocks of five cells across (M-F), but skipping weekends.
What is the best way to do this? I tried countif() with multiple ranges,
I tried sumproduct(). I even tried an array formula from Chip's website
but I can't seem to get it.

Apart from VBA, is there an easy way to do this? A pointer in the right
direction would be much appreciated.

TIA,
R.

Assuming the data is in B7:H7, and that Mon is B, Tue C, etc.,then

=SUMPRODUCT(--(ISNUMBER(MATCH(B7:H7,{"A","X"},0))),--(ISNUMBER(MATCH(MOD(COL
UMN(B7:H7),7),{2,3,4,5,6},0))))

you can change the range to suit, but the array constants {2,3,4,5,6,7} will
need to change in line with the start column, so if Mon is in C, then use
{3,4,5,6,0}

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike Echo" wrote in message
u...
I have rows of data across the spreadsheet and a total column at the
left. I need to count instances of strings ("A", "X", etc) but the
columns are not contiguous. I want to count weekdays only, so I need to
count many blocks of five cells across (M-F), but skipping weekends.
What is the best way to do this? I tried countif() with multiple ranges,
I tried sumproduct(). I even tried an array formula from Chip's website
but I can't seem to get it.

Apart from VBA, is there an easy way to do this? A pointer in the right
direction would be much appreciated.

TIA,
R.

In article ,
says...

Assuming the data is in B7:H7, and that Mon is B, Tue C, etc.,then

=SUMPRODUCT(--(ISNUMBER(MATCH(B7:H7,{"A","X"},0))),--(ISNUMBER(MATCH(MOD(COL
UMN(B7:H7),7),{2,3,4,5,6},0))))

you can change the range to suit, but the array constants {2,3,4,5,6,7} will
need to change in line with the start column, so if Mon is in C, then use
{3,4,5,6,0}

Thanks Bob, I will give this a try. Is it limited to a string length for
the formula? I have quite a few blocks of five days to do.


Thanks,
R.

Mike,

I am not sure what you are referring to when you say the string length. Can
you elucidate?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike Echo" wrote in message
u...
In article ,
says...

Assuming the data is in B7:H7, and that Mon is B, Tue C, etc.,then


=SUMPRODUCT(--(ISNUMBER(MATCH(B7:H7,{"A","X"},0))),--(ISNUMBER(MATCH(MOD(COL
UMN(B7:H7),7),{2,3,4,5,6},0))))

you can change the range to suit, but the array constants {2,3,4,5,6,7}
will
need to change in line with the start column, so if Mon is in C, then
use
{3,4,5,6,0}

Thanks Bob, I will give this a try. Is it limited to a string length for
the formula? I have quite a few blocks of five days to do.


Thanks,
R.

In article ,
says...
Mike,

I am not sure what you are referring to when you say the string length. Can
you elucidate?

With some formulae you are limited by the length of the formulae itself
(you can get around this by using named ranges). I can't be sure but I
think sumproduct was one such formula.

I have many months worth of five day blocks across my sheet.

Thanks,
R.

Hi Mike

There is no need to worry about the "blocks".
You can use a contiguous range right across the sheet if you want, as
Bob's formula is only counting the 5 weekdays and ignoring the weekends.
Just change the 2 occurrences of H7 in Bob's formula to the last column
that has data that you want to consider.

--
Regards

Roger Govier


"Mike Echo" wrote in message
u...
In article ,
says...
Mike,

I am not sure what you are referring to when you say the string
length. Can
you elucidate?

With some formulae you are limited by the length of the formulae
itself
(you can get around this by using named ranges). I can't be sure but I
think sumproduct was one such formula.

I have many months worth of five day blocks across my sheet.

Thanks,
R.