Microsoft Excel – I need to find rows that match a user entered entries and sum results of column b

20 pts.
Tags:
Duplicate records
Excel functions
Excel macros
Functions
Microsoft Excel
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: February 5, 2009  10:28 PM
UPDATED: February 10, 2009  6:13 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SbElectric
    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.
    2,540 pointsBadges:
    report
  • Ksaep04
    Thanks for the help. It worked perfect!!!
    20 pointsBadges:
    report
  • Beastwood
    Hi there, For those who are interested, over on ConstructionSoftwareReview.com we have posted a book excerpt that offers some hints on working with PivotTables and PivotCharts. --Brian
    report
  • Beastwood
    I forgot to close my LINK tag when I posted my link, so here it is again. Book excerpt: Microsoft Excel PivotChart tutorial --Brian
    900 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following