how do i count text cells in excel based on variable criteria?

[anmaka57]

I need a formula that will recalculate when the variables are changed. For
example, I will input the variables for date "1/26/2010" and BU "09" and I
want a count of how many assets that meet those criterion.

For Acctg Date: 1/26/2010
BU: 09
Asset Count:_____

BU Acctg Date
08 1/25/2010
06 1/26/2010
06 1/25/2010
09 1/25/2010
09 1/26/2010
09 1/26/2010

 

[RonaldoOneNil]

Assuming in your example the date is in B1, the BU is in B2 and your data is
in A5 to B10, enter this formula

=SUMPRODUCT((A5:A10=B2)*(B5:B10=B1))

 

[Dave Peterson]

=sumproduct(--(b10:b99=$b$1),--(a10:a99=$b$2))

Where b10:b99 hold the accounting dates, B1 holds the particular date and
A10:a99 hold the asset number and b2 holds the particular asset number.

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

=======
If you're using xl2007+, there's an =countifs() function you could use, too.