Get sum from customers A-I, J-P, Q-Z

[Reid]

Can anyone help me out. I have a list of customers and
need to get a sum for all customers A-I/J-P/Q-Z.
Subtotals won't work because the customer list changes
daily. Is there a function that will sum data based on
the condition that customer name is between certain
letters.

Thanks,
Reid

 

[Don Guillett]

try
=SUMPRODUCT((LEFT(J1:J2,1)={"a","b",etc})*(K1:K2))

 

[Dave R.]

Can anyone help me out. I have a list of customers and
need to get a sum for all customers A-I/J-P/Q-Z.
Subtotals won't work because the customer list changes

won't means never..never say never

daily. Is there a function that will sum data based on
the condition that customer name is between certain
letters.

Thanks,
Reid

there is SUMIF that should work for this with brute force.

If not, I think it'd be possible to do a sumproduct formula which looks at
the character value for the first letter of each name (e.g. a number, say
50) so that you can do ranges instead of each one like brute force.

 

[Jason Morin]

Customers in A, values in B, sum all customer A thru I:

=SUMPRODUCT((LEFT(A1:A100)>="a")*(LEFT(A1:A100)<="i")
*B1:B00)

Change the ranges if necessary.

HTH
Jason
Atlanta, GA

 

[Jim]

This will return the Group Number for the ranges specified:
=IF(AND(LEFT(A2,1)>="A",LEFT(A2,1)<="I"),"Group
1",""),IF(AND(LEFT(A2,1)>="J",LEFT(A2,1)<="P"),"Group
2",""),=IF(AND(LEFT(A2,1)>="Q",LEFT(A2,1)<="Z"),"Group 3","")

 

[Debra Dalgleish]

With your customer names in cells A2:A100 and amounts in cells E2:E100,
type a list of the ranges in cells G2:G5 --

A-I
J-P
Q-Z

In cell H2, enter the following formula, and copy down to H5 --

=SUMPRODUCT((LEFT($A$2:$A$100,1)<=RIGHT(G2,1))*(LEFT($A$2:$A$100,1)>=LEFT(G2,1))*($E$2:$E$100))

 

[Harlan Grove]

Can anyone help me out. I have a list of customers and
need to get a sum for all customers A-I/J-P/Q-Z.
Subtotals won't work because the customer list changes
daily. Is there a function that will sum data based on
the condition that customer name is between certain
letters.

Yet another alternative.

A-I: =SUMIF(Customers,"<J",Sales)
J-P: =SUMIF(Customers,">=J",Sales)-SUMIF(Customers,">=Q",Sales)
Q-Z: =SUMIF(Customers,">=Q",Sales)

 

[Don Guillett]

slight mod
=IF(AND(LEFT(J1,1)>="A",LEFT(J1,1)<="I"),"Group1",IF(AND(LEFT(J1,1)>="J",LEF
T(J1,1)<="P"),"Group2",IF(AND(LEFT(J1,1)>="Q",LEFT(J1,1)<="Z"),"Group
3","")))