I have 3 columns:
in first cell (A1) i have a code: 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.
What i have to do is to sum the quantity of the products in third cell, row 1 (A3).
Can someone help me?
Thank you!
Last edited by esential; 10-06-2009 at 07:47 AM.
to be more explicite:
Code______________Quantity
553282300000________ 50______________55
553290240040________160______________58
553480480020________420______________7E
553492300040________ 20
583182300060SMA____100
585281100060SMA____ 80
7E1382300000________15
7E1682300000________38
7E3684000000________73
7E3684000002________57
=Sumif($A$1:$A$100,C1&"*",$B$1:$B$100)
where A1:A100 hold codes and B1:B100 hold quantities
C1 holds 2-digit code "55"
Copy formula down.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
can i make this in macro?
Note that SUMIF with a wildcard will only work if column A values are text. If they are numbers try
=SUMPRODUCT((LEFT(A$1:A$100,2)=C1&"")+0,B$1:B$100)
If you wanted a macro, you needed to post in the Programming forum and explicitly say you want a macro to do this....Originally Posted by esential
Please post in the Programming forum.
Thank you! Both functions work perfectly
I posted this on Programing forum, but someone moved it.
Perhaps because the question didn't mention you wanted a macro to do the job... and it looks like you are asking for a formula.
And for the record I did not move the thread ... were your thread moved you would traditionally be notified as such.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Technically, as "Excel Worksheet Functions" is a sub-forum of "Excel Programming" then the thread still is "in the programming forum". I've never really understood why it's structured that way........
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks