Countif against a range of text entries

  • Thread starter Thread starter Hilvert Scheper
  • Start date Start date
H

Hilvert Scheper

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
 
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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top