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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Hilvert Scheper" <(E-Mail Removed)> wrote in
message news:21F5984E-9216-4595-9420-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in
> message news:21F5984E-9216-4595-9420-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in
message news:0F5DE392-82FE-4429-BA8E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:21F5984E-9216-4595-9420-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>

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" <(E-Mail Removed)> wrote in
> message news:0F5DE392-82FE-4429-BA8E-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message news:21F5984E-9216-4595-9420-(E-Mail Removed)...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>