2 questions regarding count formula

[Melinda]

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

 

[Anthony D]

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]

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

 

[Max]

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))

 

[Melinda]

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]

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.

 

[Melinda]

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

Melinda

 

[Max]

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))

 

[JMB]

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