Range Lookup for Totals? Confused

lawnG · #1 02-01-2011, 10:20 AM

***question is for excel 2003***

In the attached spreadsheet, i'm having problems getting the total hours and amount based on a certain account number.

I would like to sum the total hours and amount for account number 9 ONLY. And everything other than acct 9, sum as well.

Any help would be greatly appreciated. Thanks!

Colin Legg · #2 02-01-2011, 10:42 AM

To get the sum of column D (hours) where column B (account number) is 9, you can use this formula in cell D12:

Code:

=SUMIF(B4:B9,9,D4:D9)

To get the sum of column E (amount) where column B (account number) is 9, you can use this formula in cell E12:

Code:

=SUMIF(B4:B9,9,E4:E9)

To get the sum of column D (hours) where column B (account number) is not equal to 9, you could use either of these formulas:

Code:

=SUMIF(B4:B9,"<>9",D4:D9)
 
=D14-D12

To get the sum of column E (hours) where column B (account number) is not equal to 9, you could use either of these formulas:

Code:

=SUMIF(B4:B9,"<>9",E4:E9)
 
=E14-E12

Instead of typing the constant "9" in the formula, you could put it in a cell and reference that cell. This is generally considered to be better practice: if you change cell C12 to 9 instead of Acct 9 Total then the formulas would be:

Code:

=SUMIF(B4:B9,C12,D4:D9)
=SUMIF(B4:B9,C12,E4:E9)
=SUMIF(B4:B9,"<>"&C12,D4:D9)
=SUMIF(B4:B9,"<>"&C12,E4:E9)

etc...

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Field Lookup - Confused	lawnG	Word Tables	1	01-31-2011 03:50 PM
Creating Lookup in Excel	vikash kumar	Excel	2	12-06-2010 06:50 AM
Using the LOOKUP Command	Grapejuice	Excel	2	10-15-2008 02:02 PM
Please help with Sum formula to add totals by State! asap	dutch4fire23	Excel	0	07-28-2006 12:41 PM
Need Backup/Export - confused!	Robin12265	Outlook	2	06-04-2006 10:38 AM