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

=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 he
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 wrote:

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

Awesome! Thank you.

"Dave Peterson" wrote:

=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 he
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 wrote:

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

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 wrote:

Awesome! Thank you.

"Dave Peterson" wrote:

=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 he
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 wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

Awesome! Thank you.

"Dave Peterson" wrote:

=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 he
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 wrote:

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


--

Dave Peterson