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...

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