Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default 2 questions regarding count formula

If I have the following data

A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32

1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. p't number is between 4 and 8

2. When designing the formula, can the formula be extended in such a way
that even if I add more data to the table above, I can still get an accurate
count without redefining my data range in the formula?

Thanks.
Melinda
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 78
Default 2 questions regarding count formula

Hi Melinda,

You can use the sumproduct function with a range that is sufficient to
include new data e.g.

=SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8))

Hth
Anthony


"Melinda" wrote:

If I have the following data

A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32

1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. p't number is between 4 and 8

2. When designing the formula, can the formula be extended in such a way
that even if I add more data to the table above, I can still get an accurate
count without redefining my data range in the formula?

Thanks.
Melinda

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default 2 questions regarding count formula

Thanks Anthony for your prompt reply. However, this formula doesn't work.
Using the table below to test, the formula returns 0 instead of 2.

Any suggestions?

Melinda

"Anthony D" wrote:

Hi Melinda,

You can use the sumproduct function with a range that is sufficient to
include new data e.g.

=SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8))

Hth
Anthony


"Melinda" wrote:

If I have the following data

A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32

1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. p't number is between 4 and 8

2. When designing the formula, can the formula be extended in such a way
that even if I add more data to the table above, I can still get an accurate
count without redefining my data range in the formula?

Thanks.
Melinda

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 2 questions regarding count formula

Using the table below to test, the formula returns 0 instead of 2.

Anthony's suggestion should work. The problem lies in your data. There may
be text numbers in the numerics in cols B & C, and/or there may be extra
white spaces in the names in col A. Any of these could throw the matching
off.

This tweak should cover all possibilities:
=SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0=4),--(C2:C200+0<=8))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Melinda" wrote:
Thanks Anthony for your prompt reply. However, this formula doesn't work.
Using the table below to test, the formula returns 0 instead of 2.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default 2 questions regarding count formula

Thanks. It solve the first part of my question. However, do I have to
define the row number in my formula in order to let it work? Can I just
define the column which I want instead? Will this results in changing the
formula?

Melinda

"Max" wrote:

Using the table below to test, the formula returns 0 instead of 2.


Anthony's suggestion should work. The problem lies in your data. There may
be text numbers in the numerics in cols B & C, and/or there may be extra
white spaces in the names in col A. Any of these could throw the matching
off.

This tweak should cover all possibilities:
=SUMPRODUCT(--(TRIM(A2:A200)="Melinda"),--(B2:B200+0=1),--(C2:C200+0=4),--(C2:C200+0<=8))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Melinda" wrote:
Thanks Anthony for your prompt reply. However, this formula doesn't work.
Using the table below to test, the formula returns 0 instead of 2.



  #6   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 2 questions regarding count formula

The ranges need to be defined and should be consistent in structure, as per
the example suggested.

SUMPRODUCT doesn't accept entire col references, eg: A:A, B:B.

In defining the ranges, try to use the smallest possible range sizes,
otherwise recalc performance would be slow.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Melinda" wrote in message
...
Thanks. It solve the first part of my question. However, do I have to
define the row number in my formula in order to let it work? Can I just
define the column which I want instead? Will this results in changing the
formula?

Melinda



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 9
Default 2 questions regarding count formula

what does the "--" mean? I found that if I remove that from my formula, it
will not work. Does it have special meaning?

Melinda

"Anthony D" wrote:

Hi Melinda,

You can use the sumproduct function with a range that is sufficient to
include new data e.g.

=SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8))

Hth
Anthony


"Melinda" wrote:

If I have the following data

A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32

1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. p't number is between 4 and 8

2. When designing the formula, can the formula be extended in such a way
that even if I add more data to the table above, I can still get an accurate
count without redefining my data range in the formula?

Thanks.
Melinda

  #8   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 2 questions regarding count formula

The double unary "--" gently coerces the TRUEs/FALSEs returned by the
various match expressions (eg: TRIM(A2:A200)="Melinda") into numeric
1's/0's.

A variation of the earlier expression (without using "--")
which would work equally well here is:

=SUMPRODUCT((TRIM(A2:A200)="Melinda")*(B2:B200+0=1 )*(C2:C200+0=4)*(C2:C200+0<=8))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Melinda" wrote in message
...
what does the "--" mean? I found that if I remove that from my formula,
it
will not work. Does it have special meaning?

Melinda



  #9   Report Post  
Posted to microsoft.public.excel.newusers
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default 2 questions regarding count formula

additional details he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


"Melinda" wrote:

what does the "--" mean? I found that if I remove that from my formula, it
will not work. Does it have special meaning?

Melinda

"Anthony D" wrote:

Hi Melinda,

You can use the sumproduct function with a range that is sufficient to
include new data e.g.

=SUMPRODUCT(--(A2:A200="Melinda"),--(B2:B200=1),--(C2:C200=4),--(C2:C200<=8))

Hth
Anthony


"Melinda" wrote:

If I have the following data

A B C
1 Personnel group type p't number
2 Melinda 1 4
3 Sean 1 5
4 Melinda 1 6
5 Sean 2 30
6 Fay 3 8
7 Melinda 1 32

1. How shall I contruct my formula to count the number of data that satisfy
the following criteria:
1. personnel is Melinda
2. group type is 1
3. p't number is between 4 and 8

2. When designing the formula, can the formula be extended in such a way
that even if I add more data to the table above, I can still get an accurate
count without redefining my data range in the formula?

Thanks.
Melinda

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
Formula to count numbers Epinn Excel Worksheet Functions 10 October 30th 06 04:30 AM
Using the $ in a count formula Kim Hawk Excel Discussion (Misc queries) 2 June 17th 06 03:39 PM
Count If Formula Denise Excel Discussion (Misc queries) 2 October 14th 05 06:44 PM
Formula format for Count or Countif funtion with two criterias Debi Excel Worksheet Functions 2 September 26th 05 08:23 PM
Count If Formula Mimi Excel Worksheet Functions 0 November 5th 04 12:55 PM


All times are GMT +1. The time now is 12:19 PM.

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"