Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif()s or a better way?
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif()s or a better way?
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif()s or a better way?
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif()s or a better way?
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple countif()s or a better way?
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) | |||
Multiple countifs | Excel Worksheet Functions | |||
COUNTIFs with multiple criteria | Excel Discussion (Misc queries) | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |