What formula!?!?!
 

Hi to All,

First of all sorry for my english...

I need help with a formula in excel. I have in one sheet several
columns with data. Imagine that in column A I have a code and in column
F I have a value. I need in sheet number 2 the total of values of the
codes that finish in some values. Example:
Sheet 1
Code ---- Value
AB123 -----2
AC123 -----3
AB133 -----6
AA124 -----1
AB124 -----8

Sheet 2
Semi-code-----Total
123-------------5
133-------------6
124-------------9

This semicode can be hard coded or the right 3 digits of Code.

Thank you very much

JK


--
jamkul
------------------------------------------------------------------------
jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561
View this thread: http://www.excelforum.com/showthread...hreadid=563105

What formula!?!?!
 

Place this in Column B of sheet 2:
=SUMPRODUCT((RIGHT(Sheet1!A1:A6,3)=TEXT(Sheet2!A1, "000"))*(Sheet1!B1:B6))
You must use defined ranges, A:A and B:B do not work with SumProduct.

HTH

Die_Another_Day
jamkul wrote:
Hi to All,

First of all sorry for my english...

I need help with a formula in excel. I have in one sheet several
columns with data. Imagine that in column A I have a code and in column
F I have a value. I need in sheet number 2 the total of values of the
codes that finish in some values. Example:
Sheet 1
Code ---- Value
AB123 -----2
AC123 -----3
AB133 -----6
AA124 -----1
AB124 -----8

Sheet 2
Semi-code-----Total
123-------------5
133-------------6
124-------------9

This semicode can be hard coded or the right 3 digits of Code.

Thank you very much

JK


--
jamkul
------------------------------------------------------------------------
jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561
View this thread: http://www.excelforum.com/showthread...hreadid=563105

What formula!?!?!
 

Thanks for your reply, but it didn't work. Maybe I did some
confusion...

In sheet 1 I have something like 30000 codes and values.
In sheet 2 I have some subtotals of the codes but these subtotals were
calculated in other place (an aplication). Now I need to verify that
the results are correct. So I need to sum the values of sheet 1 if the
right(code,3) is equal to the sheet 2 code. I need to have this ready
in 1 hour... can anybody please help me??

Thanks

JK


--
jamkul
------------------------------------------------------------------------
jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561
View this thread: http://www.excelforum.com/showthread...hreadid=563105