text and number finding

**vennum6** · 08-24-2010, 10:36 AM

first, hi im new to the forum and i have no idea if this is going to be in the right place or not but im wingin it.

i got a list of "items" with a "value" beside it (i use [ ] to represent cells)
[item1] [2]
[item2] [6]
[item3] [9]

at the top i keep a list of the items and numbers and a few rows down i want to start a list keeping track of those items and values.

example:
[item1] [2]
[item2] [6]
[item3] [9]
[ ] [ ]
[ ] [ ]
[item3] [24]
[item1] [6]
[item2] [2] (so on)

i want the numbers at the top to calculate a total of each item and its value.

example:
i want item1 to search the worksheet and find everything with the text "item1" and add the value beside it.
[item1] [2] <<searches for text "item1" then ads the number beside it "6", "2+6=8"
[item2] [1]
[item3] [6]
[ ] [ ]
[ ] [ ]
[item 1] [6]

i hope this is not to confusing....if it is im sorry and ill make up a sheet to explain what i mean
thanks hope to hear something soon.

**NBVC** · 08-24-2010, 10:46 AM

Those top 3 numbers... are they fixed? Do they change?

**vennum6** · 08-25-2010, 12:52 PM

the numbers besides the items are suppose to change.

**NBVC** · 08-25-2010, 01:02 PM

I am still a little unsure of your top values.. are they starting numbers that can change with manual manipulation?

Check sample attached.

It assumes the top numbers are fixed and you want to add to these.

So formula in C1 is: =B1+SUMIF($A$6:$A$14,A1,$B$6:$B$14) copied down.

If you are not using the top 3 cells as fixed starting points, then use the formula above in B1 but eliminate the B1+ part at the beginning.

**vennum6** · 08-25-2010, 01:40 PM

ok that is pretty much it but is there a way to make it find the number and add it auto. or do you gotta do it all manual?
in the A column you have your "items" and in the B column you got "values"
is it possible to make it auto find the name of an item in A and add the number that is beside it in B to the name and number at the top?

**NBVC** · 08-25-2010, 01:45 PM

You can't have a number and a formula in the same cell.. once a number is entered, you can't have a formula use that same cell in the formula.

If you are looking to just accumulate a sum.. perhaps add the same three items to the list below and then use the formula, =SUMIF($A$6:$A$14,A1,$B$6:$B$14) and make it include those initial "hard-coded" numbers.