Hi there,
I can't seem to find any answers here that I need in particular, maybe
someone can help me out here? Sorry if the question's rather long but I'm
trying to explain exactly what I want:

I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
(Cells
F2 G2 H2 I2 J2 K2
AAA BBB CCC DDD EEE FFF

I need to know How many times All these criteria appear in Column E in sheet
"Worksheet", like:

PRODUCT:
AAA
AAA
DDD
EEE
FFF
FFF
ZZZ

(The formula should return a Total of 6)
I am currently using
COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Worksheet'!E:E,K2)

This works OK, but if I have a range of 15 cells and the column is in
another file, so this way the formula would be a Mile long. Is there a way to
count this in One formula??

Again, Sorry for the Long story, but Many Thanks for Your help!!!
Hilvert Scheper

how's this for condensed?
=SUM((E1:E65535=F2:K2)*1)
Confirm this as an array formula using (Ctrl+Shift+Enter)

Note that you can't call out the entire column for this to work (I assumed
leaving out the last row would be okay for now). If formula causes lag in
calculation speed, reduce size of range as possible.

And of course, to add more condition, simply change the width of the second
range (and don't forget to input as an array formula!)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hilvert Scheper" wrote:

> Hi there,
> I can't seem to find any answers here that I need in particular, maybe
> someone can help me out here? Sorry if the question's rather long but I'm
> trying to explain exactly what I want:
>
> I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> (Cells
> F2 G2 H2 I2 J2 K2
> AAA BBB CCC DDD EEE FFF
>
> I need to know How many times All these criteria appear in Column E in sheet
> "Worksheet", like:
>
> PRODUCT:
> AAA
> AAA
> DDD
> EEE
> FFF
> FFF
> ZZZ
>
> (The formula should return a Total of 6)
> I am currently using:
> COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Worksheet'!E:E,K2)
>
> This works OK, but if I have a range of 15 cells and the column is in
> another file, so this way the formula would be a Mile long. Is there a way to
> count this in One formula??
>
> Again, Sorry for the Long story, but Many Thanks for Your help!!!
> Hilvert Scheper

Hi Luke,
Brilliant, Thank You very much!!

Hilvert



"Luke M" wrote:

> how's this for condensed?
> =SUM((E1:E65535=F2:K2)*1)
> Confirm this as an array formula using (Ctrl+Shift+Enter)
>
> Note that you can't call out the entire column for this to work (I assumed
> leaving out the last row would be okay for now). If formula causes lag in
> calculation speed, reduce size of range as possible.
>
> And of course, to add more condition, simply change the width of the second
> range (and don't forget to input as an array formula!)
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Hilvert Scheper" wrote:
>
> > Hi there,
> > I can't seem to find any answers here that I need in particular, maybe
> > someone can help me out here? Sorry if the question's rather long but I'm
> > trying to explain exactly what I want:
> >
> > I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> > (Cells
> > F2 G2 H2 I2 J2 K2
> > AAA BBB CCC DDD EEE FFF
> >
> > I need to know How many times All these criteria appear in Column E in sheet
> > "Worksheet", like:
> >
> > PRODUCT:
> > AAA
> > AAA
> > DDD
> > EEE
> > FFF
> > FFF
> > ZZZ
> >
> > (The formula should return a Total of 6)
> > I am currently using:
> > COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Worksheet'!E:E,K2)
> >
> > This works OK, but if I have a range of 15 cells and the column is in
> > another file, so this way the formula would be a Mile long. Is there a way to
> > count this in One formula??
> >
> > Again, Sorry for the Long story, but Many Thanks for Your help!!!
> > Hilvert Scheper

would such an array-entered (i.e. insert it with CTRL+SHIFT+ENTER)
formula help?

=SUM(IF(COUNTIF($F$2:$K$2,Worksheet!$E$1:$E$15)>0,1,))


On 26 Lut, 17:48, Hilvert Scheper
<HilvertSche...@discussions.microsoft.com> wrote:
> Hi there,
> I can't seem to find any answers here that I need in particular, maybe
> someone can help me out here? Sorry if the question's rather long but I'm
> trying to explain exactly what I want:
>
> I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> (Cells
> F2 * * *G2 * * *H2 * * I2 * * *J2 * * *K2
> AAA * *BBB * *CCC * DDD * EEE * FFF
>
> I need to know How many times All these criteria appear in Column E in sheet
> "Worksheet", like:
>
> PRODUCT:
> AAA
> AAA
> DDD
> EEE
> FFF
> FFF
> ZZZ
>
> (The formula should return a Total of 6)
> I am currently using:
> COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'*!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('W*orksheet'!E:E,K2)
>
> This works OK, but if I have a range of 15 cells and the column is in
> another file, so this way the formula would be a Mile long. Is there a way to
> count this in One formula??
>
> Again, Sorry for the Long story, but Many Thanks for Your help!!!
> Hilvert Scheper

....adjust the ranges accordingly...

On 27 Lut, 15:12, Jarek Kujawa <bli...@gazeta.pl> wrote:
> would such an array-entered (i.e. insert it with CTRL+SHIFT+ENTER)
> formula help?
>
> =SUM(IF(COUNTIF($F$2:$K$2,Worksheet!$E$1:$E$15)>0,1,))
>
> On 26 Lut, 17:48, Hilvert Scheper
>
>
>
> <HilvertSche...@discussions.microsoft.com> wrote:
> > Hi there,
> > I can't seem to find any answers here that I need in particular, maybe
> > someone can help me out here? Sorry if the question's rather long but I'm
> > trying to explain exactly what I want:
>
> > I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> > (Cells
> > F2 * * *G2 * * *H2 * * I2 * * *J2 * * *K2
> > AAA * *BBB * *CCC * DDD * EEE * FFF
>
> > I need to know How many times All these criteria appear in Column E in sheet
> > "Worksheet", like:
>
> > PRODUCT:
> > AAA
> > AAA
> > DDD
> > EEE
> > FFF
> > FFF
> > ZZZ
>
> > (The formula should return a Total of 6)
> > I am currently using:
> > COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'**!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('*W*orksheet'!E:E,K2)
>
> > This works OK, but if I have a range of 15 cells and the column is in
> > another file, so this way the formula would be a Mile long. Is there a way to
> > count this in One formula??
>
> > Again, Sorry for the Long story, but Many Thanks for Your help!!!
> > Hilvert Scheper- Ukryj cytowany tekst -
>
> - Poka¿ cytowany tekst -

On Thu, 26 Feb 2009 08:48:18 -0800, Hilvert Scheper
<(E-Mail Removed)> wrote:

>Hi there,
>I can't seem to find any answers here that I need in particular, maybe
>someone can help me out here? Sorry if the question's rather long but I'm
>trying to explain exactly what I want:
>
>I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
>(Cells
>F2 G2 H2 I2 J2 K2
>AAA BBB CCC DDD EEE FFF
>
>I need to know How many times All these criteria appear in Column E in sheet
>"Worksheet", like:
>
>PRODUCT:
>AAA
>AAA
>DDD
>EEE
>FFF
>FFF
>ZZZ
>
>(The formula should return a Total of 6)
>I am currently using:
>COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Worksheet'!E:E,K2)
>
>This works OK, but if I have a range of 15 cells and the column is in
>another file, so this way the formula would be a Mile long. Is there a way to
>count this in One formula??
>
>Again, Sorry for the Long story, but Many Thanks for Your help!!!
>Hilvert Scheper


=SUMPRODUCT(--ISNUMBER(FIND(F2:K2,E1:E65535)))

1. Obviously, adjust the F2:K2 range to reflect your entire criteria range.

2. Unless you have Excel 2007+, you cannot refer to an entire column when
generating the array. Even if you could, the fewer cells, the more rapidly the
function will calculate.
--ron

Hi Jarek,
That also works Brilliantly, Thank You Very much!!
It's GREAT to know people are trying to help, Fantastic response.
Hilvert

"Jarek Kujawa" wrote:

> would such an array-entered (i.e. insert it with CTRL+SHIFT+ENTER)
> formula help?
>
> =SUM(IF(COUNTIF($F$2:$K$2,Worksheet!$E$1:$E$15)>0,1,))
>
>
> On 26 Lut, 17:48, Hilvert Scheper
> <HilvertSche...@discussions.microsoft.com> wrote:
> > Hi there,
> > I can't seem to find any answers here that I need in particular, maybe
> > someone can help me out here? Sorry if the question's rather long but I'm
> > trying to explain exactly what I want:
> >
> > I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> > (Cells
> > F2 G2 H2 I2 J2 K2
> > AAA BBB CCC DDD EEE FFF
> >
> > I need to know How many times All these criteria appear in Column E in sheet
> > "Worksheet", like:
> >
> > PRODUCT:
> > AAA
> > AAA
> > DDD
> > EEE
> > FFF
> > FFF
> > ZZZ
> >
> > (The formula should return a Total of 6)
> > I am currently using:
> > COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'Â*!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('WÂ*orksheet'!E:E,K2)
> >
> > This works OK, but if I have a range of 15 cells and the column is in
> > another file, so this way the formula would be a Mile long. Is there a way to
> > count this in One formula??
> >
> > Again, Sorry for the Long story, but Many Thanks for Your help!!!
> > Hilvert Scheper
>
>

Hi,

You can try this non array formula

SUMPRODUCT(COUNTIF(E6:E12,F2:K2))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Hilvert Scheper" <(E-Mail Removed)> wrote in
message news:E311ECF0-D7AA-42DB-874D-(E-Mail Removed)...
> Hi there,
> I can't seem to find any answers here that I need in particular, maybe
> someone can help me out here? Sorry if the question's rather long but I'm
> trying to explain exactly what I want:
>
> I have a Range of 15 Cells that contain Criteria for my Countif, looking
> like:
> (Cells
> F2 G2 H2 I2 J2 K2
> AAA BBB CCC DDD EEE FFF
>
> I need to know How many times All these criteria appear in Column E in
> sheet
> "Worksheet", like:
>
> PRODUCT:
> AAA
> AAA
> DDD
> EEE
> FFF
> FFF
> ZZZ
>
> (The formula should return a Total of 6)
> I am currently using:
> COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Worksheet'!E:E,K2)
>
> This works OK, but if I have a range of 15 cells and the column is in
> another file, so this way the formula would be a Mile long. Is there a way
> to
> count this in One formula??
>
> Again, Sorry for the Long story, but Many Thanks for Your help!!!
> Hilvert Scheper

in this case would you mind clicking on the stars to give my response
a remark?
thanks
;-)))

On 27 Lut, 17:54, Hilvert Scheper
<HilvertSche...@discussions.microsoft.com> wrote:
> Hi Jarek,
> That also works Brilliantly, Thank You Very much!!
> It's GREAT to know people are trying to help, Fantastic response.
> Hilvert
>
>
>
> "Jarek Kujawa" wrote:
> > would such an array-entered (i.e. insert it with CTRL+SHIFT+ENTER)
> > formula help?
>
> > =SUM(IF(COUNTIF($F$2:$K$2,Worksheet!$E$1:$E$15)>0,1,))
>
> > On 26 Lut, 17:48, Hilvert Scheper
> > <HilvertSche...@discussions.microsoft.com> wrote:
> > > Hi there,
> > > I can't seem to find any answers here that I need in particular, maybe
> > > someone can help me out here? Sorry if the question's rather long butI'm
> > > trying to explain exactly what I want:
>
> > > I have a Range of 15 Cells that contain Criteria for my Countif, looking like:
> > > (Cells
> > > F2 Â* Â* Â*G2 Â* Â* Â*H2 Â* Â* I2 Â* Â* Â*J2 Â* Â* Â*K2
> > > AAA Â* Â*BBB Â* Â*CCC Â* DDD Â* EEE Â* FFF
>
> > > I need to know How many times All these criteria appear in Column E in sheet
> > > "Worksheet", like:
>
> > > PRODUCT:
> > > AAA
> > > AAA
> > > DDD
> > > EEE
> > > FFF
> > > FFF
> > > ZZZ
>
> > > (The formula should return a Total of 6)
> > > I am currently using:
> > > COUNTIF('Worksheet'!E:E,F2)+COUNTIF('Worksheet'!E:E,G2)+COUNTIF('Worksheet'Â*Â*!E:E,H2)+COUNTIF('Worksheet'!E:E,I2)+COUNTIF('Worksheet'!E:E,J2)+COUNTIF('Â*WÂ*orksheet'!E:E,K2)
>
> > > This works OK, but if I have a range of 15 cells and the column is in
> > > another file, so this way the formula would be a Mile long. Is there a way to
> > > count this in One formula??
>
> > > Again, Sorry for the Long story, but Many Thanks for Your help!!!
> > > Hilvert Scheper- Ukryj cytowany tekst -
>
> - Pokaż cytowany tekst -