trying to COUNT occurrences when certain criteria is met

[Allan from Melbourne]

Bob,
Thanks for your reply but it did not assist. I received the #value response.
Perhaps someone can assist with this. See the initial question and Bob's
reply after my brief example.
Thanks
Allan

I will give you a brief example of what I am after.

Table A1 = 320
A2 = 767
A3 = 73H (NON NUMERIC VALUE)
A4 = 744
A5 = 737

The data sheet E1 = 320 L1 = 100
E2 = 737 L2 = 99
E3 = 320 L3 = 121
E4 = 747 L4 = 35
E5 = 320 L5 = 190
E6 = 767 L6 = 130
E7 = 737 L7 = 145

The result that I would expect is
320 = 2 (only 2 occurrecnces >120)
767 = 1 (only 1 occurrence > 120)
73H = 0 ( no matching data)
744 = 0 ( no OCCURRENCE > 120)
737 = 1 ( only 1 occurrence > 120)

I hope that this small example explains my problem.
By the way, what does -- indicate or mean?
Thanks again.
Allan





"Bob Phillips" wrote:

> =SUMPRODUCT(--(ISNUMBER(M
> ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000>120))
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Allan from Melbourne" <[email protected]> wrote
> in message news:[email protected]...
> > Hello
> > Hopefully some kind person can assist with this.
> > I have a worksheet with n number of rows (can vary from 30 to 3000) and
> > columns from a to m (13). I need to count the number of times that column
> L
> > value exceeds 120 when column E = a pre defined value. This pre defined
> value
> > can equal 320, 744, 743, 73H and many more.
> > I have a table set up that has these required "pre defined values". Keep
> in
> > mind that this table is dynamic, values can be added or deleted. The
> > reference for this table is on another worksheet "table" A1..A15. I don't
> > mind where this table is located, it just happens to be in this seperate
> > worksheet.
> > I can sort or filter however I was wondering if there was a better way to
> > count the occurrences.
> > Many thanks
> > Regards
> > Allan

[Toppers]

Try this and copy down for Table!A2 etc:

=SUMPRODUCT(--($E$1:$E$10000=Table!A1),--($L$1:$L$10000>120))

-- converts True False to 1/0 so calculations/counts can be done.

HTH

"Allan from Melbourne" wrote:

> Bob,
> Thanks for your reply but it did not assist. I received the #value response.
> Perhaps someone can assist with this. See the initial question and Bob's
> reply after my brief example.
> Thanks
> Allan
>
> I will give you a brief example of what I am after.
>
> Table A1 = 320
> A2 = 767
> A3 = 73H (NON NUMERIC VALUE)
> A4 = 744
> A5 = 737
>
> The data sheet E1 = 320 L1 = 100
> E2 = 737 L2 = 99
> E3 = 320 L3 = 121
> E4 = 747 L4 = 35
> E5 = 320 L5 = 190
> E6 = 767 L6 = 130
> E7 = 737 L7 = 145
>
> The result that I would expect is
> 320 = 2 (only 2 occurrecnces >120)
> 767 = 1 (only 1 occurrence > 120)
> 73H = 0 ( no matching data)
> 744 = 0 ( no OCCURRENCE > 120)
> 737 = 1 ( only 1 occurrence > 120)
>
> I hope that this small example explains my problem.
> By the way, what does -- indicate or mean?
> Thanks again.
> Allan
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(ISNUMBER(M
> > ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000>120))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Allan from Melbourne" <[email protected]> wrote
> > in message news:[email protected]...
> > > Hello
> > > Hopefully some kind person can assist with this.
> > > I have a worksheet with n number of rows (can vary from 30 to 3000) and
> > > columns from a to m (13). I need to count the number of times that column
> > L
> > > value exceeds 120 when column E = a pre defined value. This pre defined
> > value
> > > can equal 320, 744, 743, 73H and many more.
> > > I have a table set up that has these required "pre defined values". Keep
> > in
> > > mind that this table is dynamic, values can be added or deleted. The
> > > reference for this table is on another worksheet "table" A1..A15. I don't
> > > mind where this table is located, it just happens to be in this seperate
> > > worksheet.
> > > I can sort or filter however I was wondering if there was a better way to
> > > count the occurrences.
> > > Many thanks
> > > Regards
> > > Allan
>

[Bob Phillips]

Sorry, I read that as matching all values not singletons. Try

=SUMPRODUCT(--(E1:E10000=320),--(L1:L10000>120))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Allan from Melbourne" <[email protected]> wrote
in message news:[email protected]...
> Bob,
> Thanks for your reply but it did not assist. I received the #value
response.
> Perhaps someone can assist with this. See the initial question and Bob's
> reply after my brief example.
> Thanks
> Allan
>
> I will give you a brief example of what I am after.
>
> Table A1 = 320
> A2 = 767
> A3 = 73H (NON NUMERIC VALUE)
> A4 = 744
> A5 = 737
>
> The data sheet E1 = 320 L1 = 100
> E2 = 737 L2 = 99
> E3 = 320 L3 = 121
> E4 = 747 L4 = 35
> E5 = 320 L5 = 190
> E6 = 767 L6 = 130
> E7 = 737 L7 = 145
>
> The result that I would expect is
> 320 = 2 (only 2 occurrecnces >120)
> 767 = 1 (only 1 occurrence > 120)
> 73H = 0 ( no matching data)
> 744 = 0 ( no OCCURRENCE > 120)
> 737 = 1 ( only 1 occurrence > 120)
>
> I hope that this small example explains my problem.
> By the way, what does -- indicate or mean?
> Thanks again.
> Allan
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(ISNUMBER(M
> > ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000>120))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Allan from Melbourne" <[email protected]>
wrote
> > in message news:[email protected]...
> > > Hello
> > > Hopefully some kind person can assist with this.
> > > I have a worksheet with n number of rows (can vary from 30 to 3000)
and
> > > columns from a to m (13). I need to count the number of times that
column
> > L
> > > value exceeds 120 when column E = a pre defined value. This pre
defined
> > value
> > > can equal 320, 744, 743, 73H and many more.
> > > I have a table set up that has these required "pre defined values".
Keep
> > in
> > > mind that this table is dynamic, values can be added or deleted. The
> > > reference for this table is on another worksheet "table" A1..A15. I
don't
> > > mind where this table is located, it just happens to be in this
seperate
> > > worksheet.
> > > I can sort or filter however I was wondering if there was a better way
to
> > > count the occurrences.
> > > Many thanks
> > > Regards
> > > Allan
>

[Bernie Deitrick]

Allan,

Try

=SUMPRODUCT((A1 = $E$1:$E$3000)*($L$1:$L$3000>120))

Copy down to match the number of entries you have in cells A1:A15

HTH,
Bernie
MS Excel MVP


"Allan from Melbourne" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> Thanks for your reply but it did not assist. I received the #value response.
> Perhaps someone can assist with this. See the initial question and Bob's
> reply after my brief example.
> Thanks
> Allan
>
> I will give you a brief example of what I am after.
>
> Table A1 = 320
> A2 = 767
> A3 = 73H (NON NUMERIC VALUE)
> A4 = 744
> A5 = 737
>
> The data sheet E1 = 320 L1 = 100
> E2 = 737 L2 = 99
> E3 = 320 L3 = 121
> E4 = 747 L4 = 35
> E5 = 320 L5 = 190
> E6 = 767 L6 = 130
> E7 = 737 L7 = 145
>
> The result that I would expect is
> 320 = 2 (only 2 occurrecnces >120)
> 767 = 1 (only 1 occurrence > 120)
> 73H = 0 ( no matching data)
> 744 = 0 ( no OCCURRENCE > 120)
> 737 = 1 ( only 1 occurrence > 120)
>
> I hope that this small example explains my problem.
> By the way, what does -- indicate or mean?
> Thanks again.
> Allan
>
>
>
>
>
> "Bob Phillips" wrote:
>
>> =SUMPRODUCT(--(ISNUMBER(M
>> ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000>120))
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "Allan from Melbourne" <[email protected]> wrote
>> in message news:[email protected]...
>> > Hello
>> > Hopefully some kind person can assist with this.
>> > I have a worksheet with n number of rows (can vary from 30 to 3000) and
>> > columns from a to m (13). I need to count the number of times that column
>> L
>> > value exceeds 120 when column E = a pre defined value. This pre defined
>> value
>> > can equal 320, 744, 743, 73H and many more.
>> > I have a table set up that has these required "pre defined values". Keep
>> in
>> > mind that this table is dynamic, values can be added or deleted. The
>> > reference for this table is on another worksheet "table" A1..A15. I don't
>> > mind where this table is located, it just happens to be in this seperate
>> > worksheet.
>> > I can sort or filter however I was wondering if there was a better way to
>> > count the occurrences.
>> > Many thanks
>> > Regards
>> > Allan
>

[Allan from Melbourne]

Thanks to Toppers, Bob and Bernie. Your solutions worked and saved me plenty
of time and made the spreadsheet work a lot quicker.
Thanks again
Allan

"Allan from Melbourne" wrote:

> Bob,
> Thanks for your reply but it did not assist. I received the #value response.
> Perhaps someone can assist with this. See the initial question and Bob's
> reply after my brief example.
> Thanks
> Allan
>
> I will give you a brief example of what I am after.
>
> Table A1 = 320
> A2 = 767
> A3 = 73H (NON NUMERIC VALUE)
> A4 = 744
> A5 = 737
>
> The data sheet E1 = 320 L1 = 100
> E2 = 737 L2 = 99
> E3 = 320 L3 = 121
> E4 = 747 L4 = 35
> E5 = 320 L5 = 190
> E6 = 767 L6 = 130
> E7 = 737 L7 = 145
>
> The result that I would expect is
> 320 = 2 (only 2 occurrecnces >120)
> 767 = 1 (only 1 occurrence > 120)
> 73H = 0 ( no matching data)
> 744 = 0 ( no OCCURRENCE > 120)
> 737 = 1 ( only 1 occurrence > 120)
>
> I hope that this small example explains my problem.
> By the way, what does -- indicate or mean?
> Thanks again.
> Allan
>
>
>
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(ISNUMBER(M
> > ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000>120))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Allan from Melbourne" <[email protected]> wrote
> > in message news:[email protected]...
> > > Hello
> > > Hopefully some kind person can assist with this.
> > > I have a worksheet with n number of rows (can vary from 30 to 3000) and
> > > columns from a to m (13). I need to count the number of times that column
> > L
> > > value exceeds 120 when column E = a pre defined value. This pre defined
> > value
> > > can equal 320, 744, 743, 73H and many more.
> > > I have a table set up that has these required "pre defined values". Keep
> > in
> > > mind that this table is dynamic, values can be added or deleted. The
> > > reference for this table is on another worksheet "table" A1..A15. I don't
> > > mind where this table is located, it just happens to be in this seperate
> > > worksheet.
> > > I can sort or filter however I was wondering if there was a better way to
> > > count the occurrences.
> > > Many thanks
> > > Regards
> > > Allan
>