2 questions regarding count formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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))
 
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
 
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.
 
what does the "--" mean? I found that if I remove that from my formula, it
will not work. Does it have special meaning?

Melinda
 
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))
 
Back
Top