Countif against a range of text entries

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
 
B

Bernard Liengme

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
 
H

Hilvert Scheper

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
 
B

Bernard Liengme

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...
 
H

Hilvert Scheper

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif with a Range of criteria 8
Doing a countif against a substring of the range to be scanned 1
Copy addition formula of non-consecutive horizontal cells 2
Range headings 0
Dealing with the error: unable to filter on excel hc home school 0
Multiple arguments 3
Excel Countif Function - Calculating Between Two Sets of Values 3
possible countif formula? 3

Top