Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 31st 05, 05:31 PM
Greg
 
Posts: n/a
Default Find all text instances in a sheet and add one number from each row

I'm trying to have a folmula on a sheet that will look
back at another sheet and find all the instances of a text
string and then add a numeric value on that row with all
the numbers that contain the same text. For instance I
have a summary sheet that lists test names and the total
hours associated with that test. I want to look back at
another sheet and find each instance that has the same
test name and add the hours together.

This is what I want my summary sheet to do:

Summary Sheet

ID Test Name Hours
1 Test1 5
2 Test2 4


Using this data:

Data Sheet

ID Test Name Hours
1 Test1 3
2 Test2 4
3 Test3 2

I tried assigning a test ID so that I could use the
VLOOKUP function, but it only finds and reports the first
instance I find. If I manually incriment it each time it
works, but this is somewhat tedious, plus I have to
continually add on to my equation.

Thanks in advance

  #2   Report Post  
Old January 31st 05, 11:45 PM
Dave Peterson
 
Posts: n/a
Default

If those testnames are in a single column, it sounds like =sumif() would work
ok:

=SUMIF(Sheet2!A:A,B2,Sheet2!B:B)

Sheet2 column A contains the names, sheet2 column B contains the hours.

A2 contains the name to use.

Debra Dalgleish has some instructions on summing cells at:
http://www.contextures.com/xlFunctions01.html

and for counting:
http://www.contextures.com/xlFunctions04.html




Greg wrote:

I'm trying to have a folmula on a sheet that will look
back at another sheet and find all the instances of a text
string and then add a numeric value on that row with all
the numbers that contain the same text. For instance I
have a summary sheet that lists test names and the total
hours associated with that test. I want to look back at
another sheet and find each instance that has the same
test name and add the hours together.

This is what I want my summary sheet to do:

Summary Sheet

ID Test Name Hours
1 Test1 5
2 Test2 4

Using this data:

Data Sheet

ID Test Name Hours
1 Test1 3
2 Test2 4
3 Test3 2

I tried assigning a test ID so that I could use the
VLOOKUP function, but it only finds and reports the first
instance I find. If I manually incriment it each time it
works, but this is somewhat tedious, plus I have to
continually add on to my equation.

Thanks in advance


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017