Formula Help Needed

[Guest]

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 let’s say how many code 1’s there are ( I know this
can be accomplished by using the COUNTIF formula) but then I need it to take
all of the category 1’s 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.

 

[Guest]

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,1), your sumif would be =sumif(Sheet1!D,1,Sheet1!C:C).
For each instance within Sheet1!D where a 1 is found, the corresponding
element within Sheet1!C:C will be added.
--Bruce

 

[Roger Govier]

Hi

Your data on Sheet a would be ideal fro use with a Pivot Table.
Mark your range of data>Data>Pivot Table>Next>Next>Finish
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

 

[Guest]

AWESOME!! Thanks so much that did do the trick

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,1), your sumif would be =sumif(Sheet1!D,1,Sheet1!C:C).
For each instance within Sheet1!D where a 1 is found, the corresponding
element within Sheet1!C:C will be added.
--Bruce