Sumif using 1st 6 numbers

[shhhhh22]

Hello All,

My data consists of 1000s of rows in "sheet1". All the data is labeled with this format in column D: 111111abc, 111112bca, 111111def, etc.

On a separate tab "sheet2" I have a list with only account numbers in column A. So it only contains numbers. 111111, 111112, 111115, etc

How can I do a sumif if on this tab to only add sums based on 1st 6 numbers?


Thank you!

[oeldere]

with a helpcolumn in column E

E2=Left(d2,6) and drag down.

After that a pivot table to analyse the data.

[Limor_OP]

Hi,
you mean SUMIF or COUNTIF?

what do you want to sum up? which column?
Here's an example how it can be done with a COUNTIF:
=SUM(COUNTIF(Sheet2!A1,TEXT(LEFT(Sheet1!D1:D3,6),0)))

[shhhhh22]

Your right that information would help... I need a sumif... assume in your sample on sheet1 column E had totals.

[Limor_OP]

You have 2 ways (that I know of....):

=SUM(COUNTIF(Sheet2!A1,TEXT(LEFT(Sheet1!$D$1:$D$15,6),0))*(Sheet1!$E$1:$E$15))

=SUMPRODUCT((TEXT(A1,0)=LEFT(Sheet1!$D$1:$D$15,6))*(Sheet1!$E$1:$E$15))

See attached example.

[shhhhh22]

This is perfect! Thank you so much!

[Limor_OP]

you're welcome : )

[oeldere]

and a solution using pivot table.

See the attached file.