Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default count criteria within a set range in excel

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default count criteria within a set range in excel

=SUMPRODUCT(--(ISNUMBER(M
ATCH(Table!A1:A15,E1:A10000,0))),--(L1:L10000120))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Allan from Melbourne" wrote
in message ...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default count criteria within a set range in excel

Bob,
Thanks for your reply but it did not assist. I received the #value response.
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:L10000120))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Allan from Melbourne" wrote
in message ...
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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 618
Default count criteria within a set range in excel

"Allan from Melbourne" wrote
in message ...
....
By the way, what does -- indicate or mean?

....

Try http://www.mcgimpsey.com/excel/variablerate1.html
--
David Biddulph


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using A Cell In Excel as Criteria for a Query charles Excel Discussion (Misc queries) 2 July 14th 06 05:06 PM
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria Sam via OfficeKB.com Excel Worksheet Functions 2 March 20th 06 03:29 AM
How do I count items within a date range in Excel? tcolbert Excel Worksheet Functions 2 January 9th 06 07:01 PM
Can Excel count the number of scores within a range? Gatester Excel Worksheet Functions 4 December 19th 05 09:37 PM
How do I count data in range A:A that is dependent upon criteria . h2ocats Excel Worksheet Functions 1 February 21st 05 01:55 PM


All times are GMT +1. The time now is 11:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"