Excel Help

[NJLOAD]

Need help with Excel
I get a large spreadsheet at work that I need to break down by money amounts.

Example:

Zero Value
Under $1,000.00
$1,000.00 - $1,999.00
$2,000.00 - $9,999.00
$10,000.00 - $25,000.00
$25,000.00 and up

The money amounts are all in one column, so my question is there a formula that will count the totals in each category? I know how to use the count function and can do this manually but was wondering if it could all be done with one formula. Also this spreadsheet is imported from a text file so the size will vary.

Any help is appreciated.

 

[GullyFoyle]

Use combinations of the the SUMIF and COUNTIF statements to do what you want. Consult the help file for more info.

From Help file:

Suppose A1:A4 contain the following property values for four homes: $100,000, $200,000, $300,000, $400,000, respectively. B1:B4 contain the following sales commissions on each of the corresponding property values: $7,000, $14,000, $21,000, $28,000.

SUMIF(A1:A4,">160000",B1:B4) equals $63,000


Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively:

COUNTIF(A3:A6,"apples") equals 2

Suppose B3:B6 contain 32, 54, 75, 86, respectively:

COUNTIF(B3:B6,">55") equals 2

 

[NJLOAD]

Any other ideas or experts on Excel? I need a formula to look at one column and seperate all the money values shown on my first post. Is there one formula that will do this?

thanks

 

[petesamprs]

If your data starts in A1, use this formula:

=IF(A1>=25000,"$25,000 and up",IF(A1>=10000,"$10,000 - $24,999",IF(A1>=2000,"$2,000 - $9,999",IF(A1>=1000,"$1,000 - $1,999",IF(A1>0,"Under $1,000","Zero value")))))

Then copy down. Use =countif() or pivot tables to sum up each group.

 

[PowerEngineer]

Your description of what you're asking for leaves us scratching our heads...

If you want subtotals of the amounts that fall into each dollar range, then you'll obviously need several formulas in several cells, and (as Dan suggested) the SUMIF function may be your best bet.

Assuming your dollar values are in A1:A100, then

To sum the values under $1,000 in Cell B1: =SUMIF(A1:A100,"<1000")
To sum the values between $1,000 and $2,000 in Cell B2: = SUMIF(A1:A100,"<2000")-B1
To sum the values between $2,000 and $10,000 in Cell B3: = SUMIF(A1:A100,"<10000")-SUM(B1:B2)
To sum the values between $10,000 and $25,000 in Cell B4: = SUMIF(A1:A100,"<25000")-SUM(B1:B3)
To sum the values between above $25,000 in Cell B5: = SUM(A1:A100)-SUM(B1:B4)

You can count (rather than sum) the values by substituting COUNTIF for SUMIF.