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('sh eet2'!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" wrote in message
...
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('sh eet2'!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('sh eet2'!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('sh eet2'!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" wrote in message
oups.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('sh eet2'!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.