sumif question

  • Thread starter Thread starter brianwa
  • Start date Start date
B

brianwa

Is there a way to use sumif to get only a certain range of numbers
within one cell?

ie we have customers with one account with multiple ship to locations.
so the main number would be 751046. With several ship to numbers like
such, 7510460001,7510460002 ect.

Is there a way to summarize their sales by just using the left six
numbers?

Thanks
 
An array formula will work.

=SUM(IF(LEFT(B3:B6,6)="751046",C3:C6))
you must hit ctrl-shift-enter for the array formula
 
Is there a way to use sumif to get only a certain range of numbers
within one cell?

ie we have customers with one account with multiple ship to locations.
so the main number would be 751046. With several ship to numbers like
such, 7510460001,7510460002 ect.

Is there a way to summarize their sales by just using the left six
numbers?

Thanks
Click to expand...

With your 12 digit numbers in A1:A10, and the sales figures in B1:B10, and the
location of interest in C1

*Array-enter*

=SUM((MOD(A1:A10,10^6)=C1)*B1:B10)

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
try to modify this to suit.
=SUMPRODUCT((LEFT($K$54:$K$57,6)="751046")*($L$54:$L$57))
 
Back
Top