| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif against a range of text entries
Hi there,
I can't seem to find an answer here to the following problem: I have a Range of 15 Cells with Text, is there a way do do a "Countif" (or something similar) in a column in another File against All of these 15 Cells in just one formula? If I add the Countifs for all 15 cells, the formula is half a mile long and cannot be copied. Any help here is Very Welcome, Many Thanks in advance!! Rgds, Hilvert Scheper |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif against a range of text entries
What do you want to count?
Do you want to know something like how many cells in A1:A15 hold the word "apple" ? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi there, I can't seem to find an answer here to the following problem: I have a Range of 15 Cells with Text, is there a way do do a "Countif" (or something similar) in a column in another File against All of these 15 Cells in just one formula? If I add the Countifs for all 15 cells, the formula is half a mile long and cannot be copied. Any help here is Very Welcome, Many Thanks in advance!! Rgds, Hilvert Scheper |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif against a range of text entries
Hi Bernard,
Just to explain, I want to count how many times any of the 15 references appear in a column in another workbook. In my spreadsheet I have a row of 15 cells looking like: FJ206 GU625 XP279 HM071 MC043 and a Column in another workbook with: PARTNR FJ206 XP279 MC043 MC043 XP279 KM352 KM352 KM352 KM352 P825J P825J Now I want to calculate how many times ALL references in my row appear in that column. Currently I use a formula for each cell in the row and adding them with "+", like: COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,H6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,I6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,J6) Is there a way to do this in just one formula? Thanking You again for Your trouble, Hilvert Scheper "Bernard Liengme" wrote: What do you want to count? Do you want to know something like how many cells in A1:A15 hold the word "apple" ? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi there, I can't seem to find an answer here to the following problem: I have a Range of 15 Cells with Text, is there a way do do a "Countif" (or something similar) in a column in another File against All of these 15 Cells in just one formula? If I add the Countifs for all 15 cells, the formula is half a mile long and cannot be copied. Any help here is Very Welcome, Many Thanks in advance!! Rgds, Hilvert Scheper |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif against a range of text entries
In row 1 of worksheet in my first file put your row FJ206 GU625 XP279 HM071
MC043 So FJ206 is in A1, GU625 in B1, etc. In Column A of Sheet1 of another workbook (Book4) I placed your column of values In the first worksheet in A2 (under FJ206) I used the formula =COUNTIF([Book4]Sheet1!$A:$A,A1) and I copied this across row 2 The results agree with a visual count (so I got it right <grin) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi Bernard, Just to explain, I want to count how many times any of the 15 references appear in a column in another workbook. In my spreadsheet I have a row of 15 cells looking like: FJ206 GU625 XP279 HM071 MC043 and a Column in another workbook with: PARTNR FJ206 XP279 MC043 MC043 XP279 KM352 KM352 KM352 KM352 P825J P825J Now I want to calculate how many times ALL references in my row appear in that column. Currently I use a formula for each cell in the row and adding them with "+", like: COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,H6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,I6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,J6) Is there a way to do this in just one formula? Thanking You again for Your trouble, Hilvert Scheper "Bernard Liengme" wrote: What do you want to count? Do you want to know something like how many cells in A1:A15 hold the word "apple" ? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi there, I can't seem to find an answer here to the following problem: I have a Range of 15 Cells with Text, is there a way do do a "Countif" (or something similar) in a column in another File against All of these 15 Cells in just one formula? If I add the Countifs for all 15 cells, the formula is half a mile long and cannot be copied. Any help here is Very Welcome, Many Thanks in advance!! Rgds, Hilvert Scheper |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif against a range of text entries
Hi Bernard,
Thanks for Your trouble, and I apologize for the misunderstanding here. "Luke M" has given me the solution here, this Array Formula: =SUM(('Worksheet'!$B$11:$B$3000=H6:L6)*1) whereas You got it Right (Grin indeed...) however Your formula contains One criterium only (Cell A1) which You then copy across. My apologies, and again, MANY Thanks for Your help, VERY much appreciated!! Hilvert "Bernard Liengme" wrote: In row 1 of worksheet in my first file put your row FJ206 GU625 XP279 HM071 MC043 So FJ206 is in A1, GU625 in B1, etc. In Column A of Sheet1 of another workbook (Book4) I placed your column of values In the first worksheet in A2 (under FJ206) I used the formula =COUNTIF([Book4]Sheet1!$A:$A,A1) and I copied this across row 2 The results agree with a visual count (so I got it right <grin) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi Bernard, Just to explain, I want to count how many times any of the 15 references appear in a column in another workbook. In my spreadsheet I have a row of 15 cells looking like: FJ206 GU625 XP279 HM071 MC043 and a Column in another workbook with: PARTNR FJ206 XP279 MC043 MC043 XP279 KM352 KM352 KM352 KM352 P825J P825J Now I want to calculate how many times ALL references in my row appear in that column. Currently I use a formula for each cell in the row and adding them with "+", like: COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,H6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,I6)+COUNTIF('[REPORTS FEEDER 3.xls]Phase1'!B:B,J6) Is there a way to do this in just one formula? Thanking You again for Your trouble, Hilvert Scheper "Bernard Liengme" wrote: What do you want to count? Do you want to know something like how many cells in A1:A15 hold the word "apple" ? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Hilvert Scheper" wrote in message ... Hi there, I can't seem to find an answer here to the following problem: I have a Range of 15 Cells with Text, is there a way do do a "Countif" (or something similar) in a column in another File against All of these 15 Cells in just one formula? If I add the Countifs for all 15 cells, the formula is half a mile long and cannot be copied. Any help here is Very Welcome, Many Thanks in advance!! Rgds, Hilvert Scheper |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
| how can I use Excel's COUNTIF to count column entries71 but <110 | New Users to Excel | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Hybrid Mode
