Complex Sumif and Countif

  • Thread starter Thread starter nshah
  • Start date Start date
N

nshah

I want to get the averages of data points that fit a certain criteria.

I have a column of data that includes a bank's asset size and anothe
column that indicates how much was paid to acquire the bank.

my question is this, suppose i want to make a summary page that show
the average price paid for those banks with say $100 - $200 million i
assets, then an average for those banks with between $200 - $40
million, then an average for those banks with between $600 - $800 o
something

the key is, when using SUMIF and COUNTIF, how can I apply a range o
values? I am familiar with using SUMIF to simply get info if data fit
a criteria like ">500" or something but what to do when the critera i
a range
 
Use average

=AVERAGE(IF((A1:A100>=200)*(A1:A100<500),B1:B100))


entered with ctrl + shift & enter

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
The Excel studs that post here might have better answers than mine,
but this might help if you only want to use sumif and countif

I set up a table as follows :
A B C
Bank Assets Purchase Price


Bank A $6,910,877 13,821,754.0
Bank B $6,270,252 18,810,756.0
Bank C $2,338,185 7,014,555.0
Bank D $5,410,628 16,231,884.0
Bank E $4,668,370 9,336,740.0
Bank F $1,355,909 2,711,818.0
Bank G $1,080,181 3,240,543.0
Bank H $5,758,099 17,274,297.0
Bank I $1,685,125 5,055,375.0

$35,477,626 $93,497,722

and pulled the data from that table as follows :

D E F G H
Assets Number Total Paid Average Price
From To of banks Paid

$1 $1,000,000 0 $- #DIV/0!
$1,000,001 $2,500,000 4 $18,022,291 $4,505,573
$2,500,001 $5,000,000 1 $9,336,740 $9,336,740
$>5,000,000 4 $66,138,691 $16,534,673

$93,497,722

In column F you can use the following formulas:

=COUNTIF($B$4:$B$12,">1")-COUNTIF($B$4:$B$12,">100000")
=COUNTIF($B$4:$B$12,">1000000")-COUNTIF($B$4:$B$12,">2500000")
=COUNTIF($B$4:$B$12,">2500000")-COUNTIF($B$4:$B$12,">5000000")
=COUNTIF($B$4:$B$12,">5000000")

As you can probably see these are pretty simple, you just find all the
numbers greater than your lowest number in your range (like $1 in the
first one) and subtract all of the amounts greater than your highest
range ($1,000,000 in the first one)

So you get:
9-9 = 0
9-5 = 4
5-4 = 1
= 4

So that give yours you the number of banks for total paid I used the
same metholodigy...

=SUMIF($B$4:$B$12,">1",$C$4:$C$12)-SUMIF($B$4:$B$12,">1000000",$C$4:$C$12)
=SUMIF($B$4:$B$12,">1000000",$C$4:$C$12)-SUMIF($B$4:$B$12,">2500000",$C$4:$C$12)
=SUMIF($B$4:$B$12,">2500000",$C$4:$C$12)-SUMIF($B$4:$B$12,">5000000",$C$4:$C$12)
=SUMIF($B$4:$B$12,">5000000",$C$4:$C$12)

From there I think you can figure out the rest. I hope this helps you
and like I said some of the guys here might have better/more
streamlined answers (like frequency distributions)but I think this
might get you to where you need to be.

Good luck

Chad
 
Back
Top