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

  • Thread starter Thread starter Reid
  • Start date Start date
R

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
 
Reid said:
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
Click to expand...

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
Click to expand...

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.
 
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
 
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","")
 
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))
 
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.
Click to expand...

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)
 
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","")))
 
Back
Top