Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula Help Needed
I would greatly appreciate any help anyone can offer on this.
Ok let me first give a little background info. I have a spreadsheet of my raw data (spreadsheet a) it has 4 categories, account#, client name, balance amount and a code (1-7). The code tells me what internal business category each account falls under. I have another spreadsheet (spreadsheet b) that takes the raw data of a. and puts it into a neat easy to read report. What I need is a formula that will look through the entirety of spreadsheet a and first, determine lets say how many code 1s there are ( I know this can be accomplished by using the COUNTIF formula) but then I need it to take all of the category 1s it finds and then add up the balances of those accounts. So in essence I need it to find a certain modifier in column d then give me a sum from column c of only those results. I hope this makes sense. |
#2
|
|||
|
|||
Formula Help Needed
Use the SUMIF function. It's quite like COUNTIF except that you provide a
third argument, the 'sum range', indicating which cells should be added when the condition is satisfied. So if your countif function was =countif(Sheet1!D:D,1), your sumif would be =sumif(Sheet1!D:D,1,Sheet1!C:C). For each instance within Sheet1!D:D where a 1 is found, the corresponding element within Sheet1!C:C will be added. --Bruce "BDY" wrote: I would greatly appreciate any help anyone can offer on this. Ok let me first give a little background info. I have a spreadsheet of my raw data (spreadsheet a) it has 4 categories, account#, client name, balance amount and a code (1-7). The code tells me what internal business category each account falls under. I have another spreadsheet (spreadsheet b) that takes the raw data of a. and puts it into a neat easy to read report. What I need is a formula that will look through the entirety of spreadsheet a and first, determine lets say how many code 1s there are ( I know this can be accomplished by using the COUNTIF formula) but then I need it to take all of the category 1s it finds and then add up the balances of those accounts. So in essence I need it to find a certain modifier in column d then give me a sum from column c of only those results. I hope this makes sense. |
#3
|
|||
|
|||
Formula Help Needed
Hi
Your data on Sheet a would be ideal fro use with a Pivot Table. Mark your range of dataDataPivot TableNextNextFinish Drag the Code field to the Column Area, drag the Account# Field to the Row Area and drag the Balance Field to the Data area. For more help on Pivot Tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm and http://www.contextures.com/xlPivot02.html It is well worth investing a little time in learning how to use Pivot Tables Regards Roger Govier BDY wrote: I would greatly appreciate any help anyone can offer on this. Ok let me first give a little background info. I have a spreadsheet of my raw data (spreadsheet a) it has 4 categories, account#, client name, balance amount and a code (1-7). The code tells me what internal business category each account falls under. I have another spreadsheet (spreadsheet b) that takes the raw data of a. and puts it into a neat easy to read report. What I need is a formula that will look through the entirety of spreadsheet a and first, determine lets say how many code 1s there are ( I know this can be accomplished by using the COUNTIF formula) but then I need it to take all of the category 1s it finds and then add up the balances of those accounts. So in essence I need it to find a certain modifier in column d then give me a sum from column c of only those results. I hope this makes sense. |
#4
|
|||
|
|||
Formula Help Needed
AWESOME!! Thanks so much that did do the trick
"bpeltzer" wrote: Use the SUMIF function. It's quite like COUNTIF except that you provide a third argument, the 'sum range', indicating which cells should be added when the condition is satisfied. So if your countif function was =countif(Sheet1!D:D,1), your sumif would be =sumif(Sheet1!D:D,1,Sheet1!C:C). For each instance within Sheet1!D:D where a 1 is found, the corresponding element within Sheet1!C:C will be added. --Bruce "BDY" wrote: I would greatly appreciate any help anyone can offer on this. Ok let me first give a little background info. I have a spreadsheet of my raw data (spreadsheet a) it has 4 categories, account#, client name, balance amount and a code (1-7). The code tells me what internal business category each account falls under. I have another spreadsheet (spreadsheet b) that takes the raw data of a. and puts it into a neat easy to read report. What I need is a formula that will look through the entirety of spreadsheet a and first, determine lets say how many code 1s there are ( I know this can be accomplished by using the COUNTIF formula) but then I need it to take all of the category 1s it finds and then add up the balances of those accounts. So in essence I need it to find a certain modifier in column d then give me a sum from column c of only those results. I hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple (I Think) Date Formula Needed | Excel Discussion (Misc queries) | |||
Formula needed | Excel Discussion (Misc queries) | |||
More Help Needed with Count formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions |