Correct Count
 

I'm trying to count the number of names that fall with in a certain alpha
range and numerical range. I can do one or the other but can't seem to do
both. Let's say in column B, I have 20 names ranging from A to Zachary and
in column E, I have numerical values ranging from 1 to 60. I want to count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain alpha
range and numerical range. I can do one or the other but can't seem to do
both. Let's say in column B, I have 20 names ranging from A to Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

Hi,

Assuming the same layout as Biff, in 2007:

=COUNTIFS(A1:A20,"="&G1,A1:A20,"<="&H1,E1:E20,"= "&I1,E1:E20,"<="&J1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"GAIDEN" wrote:

I'm trying to count the number of names that fall with in a certain alpha
range and numerical range. I can do one or the other but can't seem to do
both. Let's say in column B, I have 20 names ranging from A to Zachary and
in column E, I have numerical values ranging from 1 to 60. I want to count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain alpha
range and numerical range. I can do one or the other but can't seem to do
both. Let's say in column B, I have 20 names ranging from A to Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain
alpha
range and numerical range. I can do one or the other but can't seem to
do
both. Let's say in column B, I have 20 names ranging from A to Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

have another question for you. using the same info as before. if i added a
column with dollar amounts, how would i add the dollar amounts for the names
that fall between A & Jackson and 11 to 20?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain
alpha
range and numerical range. I can do one or the other but can't seem to
do
both. Let's say in column B, I have 20 names ranging from A to Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

Let's assume the range to sum is F1:F20 -

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20)

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
have another question for you. using the same info as before. if i added
a
column with dollar amounts, how would i add the dollar amounts for the
names
that fall between A & Jackson and 11 to 20?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain
alpha
range and numerical range. I can do one or the other but can't seem
to
do
both. Let's say in column B, I have 20 names ranging from A to
Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want
to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

Thanks again. It worked.

"T. Valko" wrote:

Let's assume the range to sum is F1:F20 -

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20)

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
have another question for you. using the same info as before. if i added
a
column with dollar amounts, how would i add the dollar amounts for the
names
that fall between A & Jackson and 11 to 20?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a certain
alpha
range and numerical range. I can do one or the other but can't seem
to
do
both. Let's say in column B, I have 20 names ranging from A to
Zachary
and
in column E, I have numerical values ranging from 1 to 60. I want
to
count
the number of names that fall between A & Jackson and 11 to 20.

Correct Count
 

You're welcome!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
Thanks again. It worked.

"T. Valko" wrote:

Let's assume the range to sum is F1:F20 -

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20)

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
have another question for you. using the same info as before. if i
added
a
column with dollar amounts, how would i add the dollar amounts for the
names
that fall between A & Jackson and 11 to 20?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
That helped. Thank you

"T. Valko" wrote:

Use cells to hold your criteria:

G1 = A
H1 = Jackson
I1 = 11
J1 = 20

=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1))

--
Biff
Microsoft Excel MVP


"GAIDEN" wrote in message
...
I'm trying to count the number of names that fall with in a
certain
alpha
range and numerical range. I can do one or the other but can't
seem
to
do
both. Let's say in column B, I have 20 names ranging from A to
Zachary
and
in column E, I have numerical values ranging from 1 to 60. I
want
to
count
the number of names that fall between A & Jackson and 11 to 20.