| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT and IF Array
For every occurrence of a string in B9 in the current worksheet, I want to
count the number of cells that contain the letter W in Lineup Worksheet F2:F83, where the string in Lineup Worksheet B2:B83 matches the string in B9. I have tried using this formula but it counts all of the values in F2:F83; I only want a count of those contaiing W. =COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup Worksheet'!$F$2:$F$83="W",0,0))) Thanks, Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and IF Array
Try this:
=COUNTIF(Lineup!B$2:B$83,B9) Hope this helps. Pete On Dec 1, 8:15 pm, bob wrote: For every occurrence of a string in B9 in the current worksheet, I want to count the number of cells that contain the letter W in Lineup Worksheet F2:F83, where the string in Lineup Worksheet B2:B83 matches the string in B9. I have tried using this formula but it counts all of the values in F2:F83; I only want a count of those contaiing W. =COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup Worksheet'!$F$2:$F$83="W",0,0))) Thanks, Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and IF Array
=SUMPRODUCT(--($B9='Lineup Worksheet'!$B$2:$B$83),--('Lineup
Worksheet'!$F$2:$F$83="W")) "bob" wrote: For every occurrence of a string in B9 in the current worksheet, I want to count the number of cells that contain the letter W in Lineup Worksheet F2:F83, where the string in Lineup Worksheet B2:B83 matches the string in B9. I have tried using this formula but it counts all of the values in F2:F83; I only want a count of those contaiing W. =COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup Worksheet'!$F$2:$F$83="W",0,0))) Thanks, Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNT and IF Array
Hello bob,
personally I'd use SUMPRODUCT as Teethless mama suggests but the reason your formula doesn't work is that your second if returns 0 whether TRUE or FALSE - change to: =COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup Worksheet'!$F$2:$F$83="W",1))) confirmed with CTRL+SHIFT+ENTER "bob" wrote: For every occurrence of a string in B9 in the current worksheet, I want to count the number of cells that contain the letter W in Lineup Worksheet F2:F83, where the string in Lineup Worksheet B2:B83 matches the string in B9. I have tried using this formula but it counts all of the values in F2:F83; I only want a count of those contaiing W. =COUNT(IF($B9='Lineup Worksheet'!$B$2:$B$83,IF('Lineup Worksheet'!$F$2:$F$83="W",0,0))) Thanks, Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count & Array Function | Excel Worksheet Functions | |||
| Inconsistent Array Count results | Excel Worksheet Functions | |||
| Count If Array Formula | Excel Worksheet Functions | |||
| Count if array contains cells of a certain value | Excel Worksheet Functions | |||
| How can you use count with an array formula similar to using sum | Excel Worksheet Functions | |||
Linear Mode
