percentage

[Rene]

Hi, I trying to write a formula that will look in column b for 'building A'
and calculate the number of times the cost is <500, as a percentage. 2
entries but only 1 is <500. Should return 50%

a2:b100
cost location
500 building a
100 building b
250 building c
100 building a

 

[Dave Peterson]

=countif(b:b,"building a")

=sumproduct(--(b2:b100="building a"),--(a2:a100<500))
will count the number of entries that are less than 500, but have building a in
column B.

=countif(b2:b100,"building a")
will count the number of entries that have building a in column B.

So dividing:
=sumproduct(--(b2:b100="building a"),--(a2:a100<500))
/countif(b2:b100,"building a")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========
xl2007 has a new function named =countifs(). You could use that instead of the
=sumproduct() portion.

 

[Rene]

Awesome! Thank you.

=countif(b:b,"building a")

=sumproduct(--(b2:b100="building a"),--(a2:a100<500))
will count the number of entries that are less than 500, but have building a in
column B.

=countif(b2:b100,"building a")
will count the number of entries that have building a in column B.

So dividing:
=sumproduct(--(b2:b100="building a"),--(a2:a100<500))
/countif(b2:b100,"building a")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========
xl2007 has a new function named =countifs(). You could use that instead of the
=sumproduct() portion.

 

[Dave Peterson]

Glad you got it working.

(I meant to delete that first line when I changed the description around--but I
screwed up and missed it!)

 

[Rene]

I added ... it worked.
SUMPRODUCT(--(B2:B100="a"),--(A2:A100>150),--(A2:A100<500)/COUNTIF(B2:B100,"a"))