Formula/function help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a formula that perform a calculation if text of certain cells matches
Example
A B C D
1) Customer Procedure Time Quoted Total Actual Time Actual Tim
2) Jones B .8 .75 .7
3) Smith B 2.0 1.95 1.9
4) Jones A / / .4
5) Jones A 1.0 1.05 .6
6) Smith A 6.0 5. 9 5.
So, if A2 and B2 match A3:A6 and B3:B6 I want to total the corresponding E column and add it to the bottom-most procedure. Column C is manually entered data. Thanks in advance.
 
Steve St.Jean said:
I am trying to create a formula that perform a calculation if
text of certain cells matches.
Click to expand...
....

Excessive spacing reduced. Don't use tabs in newsgroup postings. They're
usually screwed up by newsreaders converting them into spaces.
A B C D E
1) Customer Procedure Time Quoted Total Actual Time Actual Time
2) Jones B .8 .75 .75
3) Smith B 2.0 1.95 1.95
4) Jones A / / .45
5) Jones A 1.0 1.05 .6
6) Smith A 6.0 5.9 5.9

So, if A2 and B2 match A3:A6 and B3:B6 I want to total the
corresponding E column and add it to the bottom-most procedure.
Column C is manually entered data. Thanks in advance.
Click to expand...

Unclear. In the table above, B2 matches nothing in B3:B6. Give an example
that shows a match and the result you want.
 
I am trying to create a formula that perform a calculation if text of certain cells matches
Example
A B C D
1) Customer Procedure Time Quoted Total Actual Time Actual Tim
2) Jones B .8 .75 .7
3) Smith B 2.0 1.95 1.9
4) Jones A / / .4
5) Jones A 1.0 1.05 .6
6) Smith A 6.0 5. 9 5.
So, if A4 and B4 match A2:A6 and B2:B6(EXCEPT A4/B4) I want to total the corresponding E column and add it to the bottom-most procedure. the procedure would be repeated for each row. Columns C & E are manually entered data. Thanks again.
 
Just wondering whether a pivot table might accomplish what you're apparently
after?

Here's some quick steps to set it up [in xl97]
------------------------------------------------------------
Assuming your sample data set is in Sheet1, A1:E6,
with headers in row1, data in rows2 - 6

Click on any cell inside the data-set

Click Data > Pivot table report > Next

In Step 2 of the wizard, the range $A$1:$E$6 would be shown
[ Excel usually guesses correctly, but you can correct the range here]

Click Next

In Step 3 of the wizard:
------------------------------
Drag Customer and drop within ROW area
Drag Procedure and drop within ROW area [below Customer]

Drag Actual Time and drop within DATA area
[ it should read "Sum of Actual Time" ]

Click Finish

The pivot table will be created in a new sheet just to the left of Sheet1
and would appear as:

Sum of Actual Time
Customer Procedure Total
Jones.........A................1.05
.....................B................0.75
Jones Total..................1.8
Smith.........A.................5.9
.....................B................1.95
Smith Total .................7.85
Grand Total.................9.65

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Steve St.Jean said:
I am trying to create a formula that perform a calculation if text of certain cells matches.
Example;
A B C D E
1) Customer Procedure Time Quoted Total Actual Time Actual Time
2) Jones B .8 ..75 .75
3) Smith B 2.0 1.95 1.95
4) Jones A / / .45
5) Jones A 1.0 1.05 .6
6) Smith A 6.0 5. 9 5.9
So, if A4 and B4 match A2:A6 and B2:B6(EXCEPT A4/B4) I want to total the
Click to expand...
corresponding E column and add it to the bottom-most procedure. the
procedure would be repeated for each row. Columns C & E are manually entered
data. Thanks again.
 
Back
Top