Correct Count

[GAIDEN]

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.

 

[T. Valko]

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

 

[Shane Devenshire]

Hi,

Assuming the same layout as Biff, in 2007:

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

 

[GAIDEN]

That helped. Thank you

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[GAIDEN]

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]

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)

 

[GAIDEN]

Thanks again. It worked.

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)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks again. It worked.