Use CountIF with conditional formulas

I have a list of names (it's an Exchange GAL extract), and I'm trying to
count all the names that fall between a range of letters, i.e. 'A-F', 'G-L',
'M-R', S-Z'.

Right now, I've added a column to the extract page that uses a conditional
IF to populate some text into the cell that reflects which range the First
letter of the Lastname falls in. This is then filled down the column until I
hit the last populated row:

=IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6), "SG2" ...(and so on)

I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
This does work, but is dependant on the manual process of dragging the IF
formula down the set of records.

There has to be an easier way to bypass the manual IF step and do the
CountIF across any list of rows, but I can't figure out how to get the
criteria in properly.

Thanks in advance,

Chris.

=SUMPRODUCT(--(LEFT(A1:A28,1)>="A"),--(LEFT(A1:A28,1)<="F"))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Price" <ChrisPrice@discussions.microsoft.com> wrote in message
news:866C0F4D-597D-472B-ACB4-B16B940988D5@microsoft.com...
> I have a list of names (it's an Exchange GAL extract), and I'm trying to
> count all the names that fall between a range of letters, i.e. 'A-F',
'G-L',
> 'M-R', S-Z'.
>
> Right now, I've added a column to the extract page that uses a conditional
> IF to populate some text into the cell that reflects which range the First
> letter of the Lastname falls in. This is then filled down the column until
I
> hit the last populated row:
>
> =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
>
IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6
), "SG2" ...(and so on)
>
> I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> This does work, but is dependant on the manual process of dragging the IF
> formula down the set of records.
>
> There has to be an easier way to bypass the manual IF step and do the
> CountIF across any list of rows, but I can't figure out how to get the
> criteria in properly.
>
> Thanks in advance,
>
> Chris.

=SUMPRODUCT(--(LEFT(A1:A28,1)>="A"),--(LEFT(A1:A28,1)<="F"))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Price" <ChrisPrice@discussions.microsoft.com> wrote in message
news:866C0F4D-597D-472B-ACB4-B16B940988D5@microsoft.com...
> I have a list of names (it's an Exchange GAL extract), and I'm trying to
> count all the names that fall between a range of letters, i.e. 'A-F',
'G-L',
> 'M-R', S-Z'.
>
> Right now, I've added a column to the extract page that uses a conditional
> IF to populate some text into the cell that reflects which range the First
> letter of the Lastname falls in. This is then filled down the column until
I
> hit the last populated row:
>
> =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
>
IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6
), "SG2" ...(and so on)
>
> I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> This does work, but is dependant on the manual process of dragging the IF
> formula down the set of records.
>
> There has to be an easier way to bypass the manual IF step and do the
> CountIF across any list of rows, but I can't figure out how to get the
> criteria in properly.
>
> Thanks in advance,
>
> Chris.

Try
SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F

-sk

Chris Price wrote:
> I have a list of names (it's an Exchange GAL extract), and I'm trying to
> count all the names that fall between a range of letters, i.e. 'A-F', 'G-L',
> 'M-R', S-Z'.
>
> Right now, I've added a column to the extract page that uses a conditional
> IF to populate some text into the cell that reflects which range the First
> letter of the Lastname falls in. This is then filled down the column until I
> hit the last populated row:
>
> =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6), "SG2" ...(and so on)
>
> I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> This does work, but is dependant on the manual process of dragging the IF
> formula down the set of records.
>
> There has to be an easier way to bypass the manual IF step and do the
> CountIF across any list of rows, but I can't figure out how to get the
> criteria in properly.
>
> Thanks in advance,
>
> Chris.

I get a #Num error when using the just the Column - it works correctly when I
use just the range with records.

Thanks for your help.

Chris.

"sk" wrote:

>
> Try
> SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F
>
> -sk
>
> Chris Price wrote:
> > I have a list of names (it's an Exchange GAL extract), and I'm trying to
> > count all the names that fall between a range of letters, i.e. 'A-F', 'G-L',
> > 'M-R', S-Z'.
> >
> > Right now, I've added a column to the extract page that uses a conditional
> > IF to populate some text into the cell that reflects which range the First
> > letter of the Lastname falls in. This is then filled down the column until I
> > hit the last populated row:
> >
> > =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> > IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6), "SG2" ...(and so on)
> >
> > I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> > This does work, but is dependant on the manual process of dragging the IF
> > formula down the set of records.
> >
> > There has to be an easier way to bypass the manual IF step and do the
> > CountIF across any list of rows, but I can't figure out how to get the
> > criteria in properly.
> >
> > Thanks in advance,
> >
> > Chris.
>
>

I get a #Num error when using the just the Column - it works correctly when I
use just the range with records.

Thanks for your help.

Chris.

"sk" wrote:

>
> Try
> SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F
>
> -sk
>
> Chris Price wrote:
> > I have a list of names (it's an Exchange GAL extract), and I'm trying to
> > count all the names that fall between a range of letters, i.e. 'A-F', 'G-L',
> > 'M-R', S-Z'.
> >
> > Right now, I've added a column to the extract page that uses a conditional
> > IF to populate some text into the cell that reflects which range the First
> > letter of the Lastname falls in. This is then filled down the column until I
> > hit the last populated row:
> >
> > =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> > IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6), "SG2" ...(and so on)
> >
> > I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> > This does work, but is dependant on the manual process of dragging the IF
> > formula down the set of records.
> >
> > There has to be an easier way to bypass the manual IF step and do the
> > CountIF across any list of rows, but I can't figure out how to get the
> > criteria in properly.
> >
> > Thanks in advance,
> >
> > Chris.
>
>

SUMPRODUCT will not work on the whole column, it must be a designated range

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sk" <kukreti.sharad@gmail.com> wrote in message
news:1122419873.565203.250510@z14g2000cwz.googlegroups.com...
>
> Try
> SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F
>
> -sk
>
> Chris Price wrote:
> > I have a list of names (it's an Exchange GAL extract), and I'm trying to
> > count all the names that fall between a range of letters, i.e. 'A-F',
'G-L',
> > 'M-R', S-Z'.
> >
> > Right now, I've added a column to the extract page that uses a
conditional
> > IF to populate some text into the cell that reflects which range the
First
> > letter of the Lastname falls in. This is then filled down the column
until I
> > hit the last populated row:
> >
> > =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> >
IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6
), "SG2" ...(and so on)
> >
> > I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the
totals.
> > This does work, but is dependant on the manual process of dragging the
IF
> > formula down the set of records.
> >
> > There has to be an easier way to bypass the manual IF step and do the
> > CountIF across any list of rows, but I can't figure out how to get the
> > criteria in properly.
> >
> > Thanks in advance,
> >
> > Chris.
>

SUMPRODUCT will not work on the whole column, it must be a designated range

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sk" <kukreti.sharad@gmail.com> wrote in message
news:1122419873.565203.250510@z14g2000cwz.googlegroups.com...
>
> Try
> SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F
>
> -sk
>
> Chris Price wrote:
> > I have a list of names (it's an Exchange GAL extract), and I'm trying to
> > count all the names that fall between a range of letters, i.e. 'A-F',
'G-L',
> > 'M-R', S-Z'.
> >
> > Right now, I've added a column to the extract page that uses a
conditional
> > IF to populate some text into the cell that reflects which range the
First
> > letter of the Lastname falls in. This is then filled down the column
until I
> > hit the last populated row:
> >
> > =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> >
IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6
), "SG2" ...(and so on)
> >
> > I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the
totals.
> > This does work, but is dependant on the manual process of dragging the
IF
> > formula down the set of records.
> >
> > There has to be an easier way to bypass the manual IF step and do the
> > CountIF across any list of rows, but I can't figure out how to get the
> > criteria in properly.
> >
> > Thanks in advance,
> >
> > Chris.
>

Try
SUMPRODUCT(--(LEFT(D:D)>="A"),--(LEFT(D:D)<="F")) for bracket A-F

-sk

Chris Price wrote:
> I have a list of names (it's an Exchange GAL extract), and I'm trying to
> count all the names that fall between a range of letters, i.e. 'A-F', 'G-L',
> 'M-R', S-Z'.
>
> Right now, I've added a column to the extract page that uses a conditional
> IF to populate some text into the cell that reflects which range the First
> letter of the Lastname falls in. This is then filled down the column until I
> hit the last populated row:
>
> =IF(LEFT('sheet2'!D2,1)<='sheet1'!$C$5, "SG1",
> IF(AND(LEFT('sheet2'!D2,1)>='sheet1'!$A$6,LEFT('sheet2'!D2,1)<='sheet1'!$C$6), "SG2" ...(and so on)
>
> I then do a set of simple =countif('sheet2'!A:A,"SG1") to get the totals.
> This does work, but is dependant on the manual process of dragging the IF
> formula down the set of records.
>
> There has to be an easier way to bypass the manual IF step and do the
> CountIF across any list of rows, but I can't figure out how to get the
> criteria in properly.
>
> Thanks in advance,
>
> Chris.