PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Worksheet Functions > Correct Count

Reply
Thread Tools Rate Thread

Correct Count

 
 
GAIDEN
Guest
Posts: n/a
 
      3rd Mar 2009
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.
 
Reply With Quote
 
 
 
 
T. Valko
Guest
Posts: n/a
 
      3rd Mar 2009
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" <(E-Mail Removed)> wrote in message
news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
> 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.
 
Reply With Quote
 
Shane Devenshire
Guest
Posts: n/a
 
      3rd Mar 2009
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.
 
Reply With Quote
 
GAIDEN
Guest
Posts: n/a
 
      4th Mar 2009
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" <(E-Mail Removed)> wrote in message
> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
> > 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.
>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      4th Mar 2009
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GAIDEN" <(E-Mail Removed)> wrote in message
news:BEE4D193-00F9-4A16-AA33-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
>> > 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.
>>
>>
>>
 
Reply With Quote
 
GAIDEN
Guest
Posts: n/a
 
      4th Mar 2009
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" <(E-Mail Removed)> wrote in message
> news:BEE4D193-00F9-4A16-AA33-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      4th Mar 2009
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" <(E-Mail Removed)> wrote in message
news:639646D3-7F36-4266-BF05-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:BEE4D193-00F9-4A16-AA33-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>
 
Reply With Quote
 
GAIDEN
Guest
Posts: n/a
 
      4th Mar 2009
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" <(E-Mail Removed)> wrote in message
> news:639646D3-7F36-4266-BF05-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:BEE4D193-00F9-4A16-AA33-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      4th Mar 2009
You're welcome!

--
Biff
Microsoft Excel MVP


"GAIDEN" <(E-Mail Removed)> wrote in message
news:B224AA13-B2C8-4BA9-8CC6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:639646D3-7F36-4266-BF05-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:BEE4D193-00F9-4A16-AA33-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message
>> >> >> news:F2FD0117-D750-4131-A19B-(E-Mail Removed)...
>> >> >> > 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
 
Reply With Quote
 
 
 
Reply

« function stumped | How do I find a macro in Excel? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get the status bar count correct? Lindie Microsoft Outlook Discussion 0 2nd Jun 2008 08:07 PM
count is not correct. =?Utf-8?B?Y2hlcnJ5bmljaA==?= Microsoft Access Queries 2 14th Oct 2004 02:29 PM
Correct count of files on XP Mozzy Windows XP General 2 10th Sep 2004 02:44 PM
Correct count of files on XP Mozzy Anti-Virus 1 9th Sep 2004 05:08 PM
Mail count is not correct =?Utf-8?B?UGFjaGE=?= Microsoft Outlook 4 5th Feb 2004 09:47 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 11:25 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top