Complex Sumif and Countif

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
 
P

Peo Sjoblom

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
 
R

Ron Rosenfeld

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 another
column that indicates how much was paid to acquire the bank.

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

the key is, when using SUMIF and COUNTIF, how can I apply a range of
values? I am familiar with using SUMIF to simply get info if data fits
a criteria like ">500" or something but what to do when the critera is
a range?
Click to expand...

In general, to apply a range of values using COUNTIF or SUMIF:
Assume you want to obtain a range where the lower bound is Crit1 and
the upper bound is Crit2:

=COUNTIF(rng,">=" & Crit1) - COUNTIF(rng,">" & Crit2)


--ron
 
P

Peo Sjoblom

<=--"1/31/4"

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

Regards,

Peo Sjoblom


HeatherM said:
I have a similar question ... and saw the response and I thought my
Click to expand...
formula was right -- however, it's returning 0 not what it should --
thoughts?
 
H

HeatherM

a follow up to my question -- when I use cntrl+shift+enter ... it's taking out the zero -- the basic gist is that I need to sum on column if it's within a date range and meets a certain criteri

THANKS
 
H

HeatherM

THANK YOU! It worked -- but 'warning' I'll be back with more questions now. THANK YOU!
 
C

chadt74

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
 
Top