sum one column based on another

[scalvitto0815]

i have 100 lines of data A10 to B 10 col A has code numbers and column B has amounts. Is there a foruma i can enter in b2 that will give me the sum of allamounts in Col B based on the fiorst position of the codes in Col A. I would like to enter the code in A1

 

[etaf]

a sumif() may help or sumifs()

SUMIF(B2,B10,A2:A10,A1)

but i'm not sure i follow exactly what you are after

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[SanjayGMusafir]

SUMIFS will do that but you have to explain it better.

 

[scalvitto0815]

 

[SanjayGMusafir]

Try this -

=SUMIFS(B12:B17,A12:A17,A1&"*")

 

[SanjayGMusafir]

View attachment 98388

Your codes are actually like this. Or this is just a sample data?

 

[scalvitto0815]

this is sample data
That returned 0. It needs to use only the first position of the codes in a12:a17

 

[SanjayGMusafir]

this is sample data

I checked it was returning 0 for some reason unknown to me. That's why I asked the kind of code you have.

If you can share actual data type would help better. Also, the kind of criteria you want to total on...

 

[scalvitto0815]

i got it to almost work. cells a12:a17 are numbers. cell a1 is a number. it is not returnong an answer in b1.
If i put an ' in the front of the codes, then it works, but I dont want to have to do that because the cosdes are coming from somplace else

 

[scalvitto0815]

I checked it was returning 0 for some reason unknown to me. That's why I asked the kind of code you have.

If you can share actual data type would help better. Also, the kind of criteria you want to total on...

i cant get the add in to work thats why i posted a pic