Microsoft Excel - I need to find rows that match a user entered entries and sum results of column b
Home > IT Answers > Microsoft Windows > Microsoft Excel - I need to find rows that ma...
Ksaep04
20 pts.
0
Q:
Microsoft Excel - I need to find rows that match a user entered entries and sum results of column b
Microsoft Excel, Excel functions, Functions, Excel macros, Duplicate records, SUM function
I have a spread sheet that looks like this

Project Well Hours
Lewis 1 12
Lewis 2 3
Lewis 1 9

I want to find all the rows that match a user inputed name (in this case 'Lewis'), then add the numbers in column c labeled 'hours', if the sum equals 24 then no problem, but if the sum does not equal 24 then highlight those rows in red. Any suggestions?
ASKED: Feb 5 2009  10:28 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
SbElectric
1625 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
This is a classic case of using the power of EXCEL Pivot table.
In Excel 2007 – just set up a Pivot table with Project under the “Row Label” & Sum of Hours under “Value”. Now use Conditional Formatting for values lower than 24. Here is an example:
Project Well Hours
Lewis 1 12
Lewis 2 3
Lewis 1 9
Smith 2 8
Smith 1 10 Row Labels Sum of Hours
Smith 1 4 Lewis 24
Smith 22
Total 46 Grand Total 46


I realize "Smith 22" is not showing in Red color since this Text Editor does not retain color.
Last Answered: Feb 8 2009  5:27 AM GMT by SbElectric   1625 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SbElectric   1625 pts.  |   Feb 8 2009  5:35AM GMT

Sorry the table looks awful. I copy/pasted the excel spreadsheet – but this Text Editor does not maintain the formatting.

Hope you got the idea – just experiment with Pivot table and Conditional Formatting.
Please post your experience & if you need more details.

 

Ksaep04   20 pts.  |   Feb 9 2009  4:40PM GMT

Thanks for the help. It worked perfect!!!

 

Beastwood   55 pts.  |   Feb 10 2009  6:06PM GMT

Hi there,
For those who are interested, over on <a href="http://ConstructionSoftwareReview.com" title="http://ConstructionSoftwareReview. " target="_blank">ConstructionSoftwareReview.com</a> we have posted a book excerpt that offers some hints on working with PivotTables and PivotCharts.
–Brian

 

Beastwood   55 pts.  |   Feb 10 2009  6:13PM GMT

I forgot to close my LINK tag when I posted my link, so here it is again.
Book excerpt: Microsoft Excel PivotChart tutorial
–Brian

 
0