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