summing up the number of times a text string appears in a range question.

Hi,

Can this be done?

I need a spread sheet that keeps track of work items completed at a job site. The problem is that I have about 150 items to keep track of. Here's an example:
Let's say the following values are placed in cells A1 to J1. Please note that the items in "" are text and the numbers represent the quantity.

2 "VM5-4" 1 "vm5-20" 5 "vm5-29" 6 "e1-4" 5 "VM5-4"

Now I want to sum up all like items which could appear anywhere in a range. My first thought was to use the COUNTIF function which works great if I only have one of any item - but I have more than one item.

So... is there any way I could search a range for a certain text and when it finds a match get it to look in the adjacent box for the quantity and add that to a total?

Thanks...

Hi mbu,

Can you use:

=SUMIF(B1:B100,"VM5-4",A1:A100)

This will sum all the values in column A where the corresponding cell in column B is VM5-4.

Originally Posted by pjoaquin

Hi mbu,

Can you use:

=SUMIF(B1:B100,"VM5-4",A1:A100)

This will sum all the values in column A where the corresponding cell in column B is VM5-4.

pjoaquin,

Hmmm.. that's a good idea! But...I'll need it to go one step farther.

How can that instruction be written to jump to the next 2 columns and do the same thing again and still keep the totals? In other words, how can it pick up the values in Column A9 and A10 in my example?

Thanks!

Mbu,

I'm not sure what you mean. Columns are not named A9 and A10, those are cells. Columns are A, B, C, .. BB, BC, BD.. Rows are 1, 2, 3, .. 9, 10, 11..

If you want to sum column A whenever column B is "VM5-4" AND sum column G when column H is "VM5-4", just add two SUMIF's together, like so:

Please Login or Register  to view this content.