Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mike Echo
 
Posts: n/a
Default Multiple countif()s or a better way?

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Multiple countif()s or a better way?

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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Multiple countif()s or a better way?

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Multiple countif()s or a better way?

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.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Multiple countif()s or a better way?

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)

"Roger Govier" wrote in message
...
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.





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Multiple countif()s or a better way?

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" wrote in message
u...
In article ,
says...

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.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Multiple countif()s or a better way?

Hi Bob
I know the feeling!!!<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
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)

"Roger Govier" wrote in message
...
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.







  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Multiple countif()s or a better way?

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)

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

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
selecting multiple sheet tabs and open another workbook Bannor Excel Discussion (Misc queries) 5 November 25th 05 03:38 AM
Multiple countifs ozcank Excel Worksheet Functions 2 November 14th 05 11:36 AM
COUNTIFs with multiple criteria Cene K Excel Discussion (Misc queries) 5 October 28th 05 10:43 PM
Adding multiple worksheets Craig Excel Worksheet Functions 1 July 6th 05 07:21 PM
Can I get the mode, min, and max with multiple criteria? BobT Excel Discussion (Misc queries) 1 February 15th 05 04:20 AM


All times are GMT +1. The time now is 11:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"