Use CountIF with conditional formulas

[Guest]

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.

 

[Bob Phillips]

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

etc.

--

HTH

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

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)

 

[sk]

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

-sk

 

[Guest]

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.

 

[Bob Phillips]

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)

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

-sk

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