Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of cells under a condition
I have data in the range A1:K2000. In column C I have descriptions for
different types of business expense. In column G I have the amounts of these expenses. I want a formula that searches column C for a specific business expense and when finding it, adds up all the related values in column G. How do I do this? -- Thank you, Colin. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of cells under a condition
Hi Colin,
I believe that AutoFilter will achieve what you want. You can then use the subtotal function which can sum the cells in the visible rows. Rather than me going into a long explanation I suggest that you look up both in help and then if you still have further questions then get back to me. Subtotal function gives you a variety of options for summing, averaging, counting etc. A tip when using Autofilter: Insert 3 to 4 blank rows above the column headers of your data. Select the first cell under the column header in column A and then Freeze Panes. You can then place the subtotal function above the headers in the frozen pane where you can always see the totals irrespective of the scrolling of the rest of the worksheet. Regards, OssieMac "Colin" wrote: I have data in the range A1:K2000. In column C I have descriptions for different types of business expense. In column G I have the amounts of these expenses. I want a formula that searches column C for a specific business expense and when finding it, adds up all the related values in column G. How do I do this? -- Thank you, Colin. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of cells under a condition
For example,
=SUMIF($C$1:$C$2000, "Reimbursable Travel", $G$1:$G$2000) or =SUMIF($C$1:$C$2000, H1, $G$1:$G$2000) if column H has a category list. - David Colin wrote: I have data in the range A1:K2000. In column C I have descriptions for different types of business expense. In column G I have the amounts of these expenses. I want a formula that searches column C for a specific business expense and when finding it, adds up all the related values in column G. How do I do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of cells under a condition
Many thanks David, so simple, and works a dream.
Thank you for you input Ossiemac, your solution would work but I required a formula in this instance. -- Colin. "David Hilberg" wrote: For example, =SUMIF($C$1:$C$2000, "Reimbursable Travel", $G$1:$G$2000) or =SUMIF($C$1:$C$2000, H1, $G$1:$G$2000) if column H has a category list. - David Colin wrote: I have data in the range A1:K2000. In column C I have descriptions for different types of business expense. In column G I have the amounts of these expenses. I want a formula that searches column C for a specific business expense and when finding it, adds up all the related values in column G. How do I do this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of cells under a condition
You're welcome - Thanks for the feedback!
- David Colin wrote: Many thanks David, so simple, and works a dream. Thank you for you input Ossiemac, your solution would work but I required a formula in this instance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells with condition | Excel Worksheet Functions | |||
Locking/Unlocking cells upon condition | Excel Discussion (Misc queries) | |||
Hiding cells on condition | Excel Worksheet Functions | |||
sum of a cell if 2 cells meet a condition | Excel Worksheet Functions | |||
How to add cells wiht the condition of the former cell. | Excel Discussion (Misc queries) |