Multiple countif()s or a better way?

[Mike Echo]

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.

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

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]

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.

 

[Roger Govier]

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.

 

[Mike Echo]

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.

I spose it does help if you understand wtf the formula is doing.

Thanks for clarifying, Roger.
R.

 

[Bob Phillips]

Thanks Roger. I thought I had explained that, but what was clear in my mind
wasn't necessarily clear on paper <vbg>

Bob

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

WTF? This is a family group Mike <vbg>

BTW, the problem with long formulae applies to any function, it is an Excel
restriction, whereby you can only have 7 nested functions, and/or 255
characters entered (you can frig more, but only enter 255), at least until
Office 12.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mike Echo]

WTF? This is a family group Mike <vbg>

Sorry kids. ;-)

BTW, the problem with long formulae applies to any function, it is an Excel
restriction, whereby you can only have 7 nested functions, and/or 255
characters entered (you can frig more, but only enter 255), at least until
Office 12.

Yes, that's what I was waffling on about before. I am still tinkering
and will try your glorious formula shortly. I'll let you know how I go.

Thanks again, Bob.
R.

 

[Roger Govier]

Hi Bob
I know the feeling!!!<bg>

 

[Mike Echo]

Well, she works beautifully. I didn't get the {3,4,5,6,0} bit until I
realised that you were grouping cells into blocks of 7 (MOD()) and then
determining which ones should be matched ({3,4,5,6,0}). The 0 threw me
until I remembered computers always start at 0 (my Monday is in cell Q,
a 3). Is this basically how it works?

Thanks very much for taking the time, Bob.
R.

 

[Bob Phillips]

Not quite Mike. It is not because computers start at 0, but because whenever
you MOD a value, the possible results are 0... divisor-1. So
MOD(some_Value,7) can only return values of 0,1,2,3,4,5,6.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)