Adding based on Multiple Criteria?

[Guest]

I'm sure there's an easy solution to this, but I can't figure it out... can
anyone assist?

I'm currently working on a budget for our company. We code things by
project (two letter code, example "CB") and each project has the same account
numbers (ex: 5421).

We previously had each project's budget on individual worksheets, but it's
so inefficient and I'm trying to get my supervisor away from that system--
hence I'm working towards a database.

My question is this-- how can I total what I've entered from the database
based on the two codes? Example: I would like a cell to calculate all "CB
5421" totals. Right now I have the project and the account number in two
seperate columns.

I've used the following calculation:
=SUM(IF((Expenditures!$B2:$B10000="CB")*(Expenditures!$C2:$C10000=5421),Expenditures!H2:H10000))

The problem with this is that Excel 97 isn't allowing me to just select an
entire column. Maybe I'm just being anal, but I'd like to have the formula
more like:
=SUM(IF((Expenditures!$B:$B="CB")*(Expenditures!$C:$C=5421),Expenditures!H:H))

Is there a better way to calculate this? or do I have something wrong with
it?

Audra

 

[PCLIVE]

Though you still have to specify the range, you can use:
=SUMPRODUCT(--(Expenditures!$B2:$B10000="CB"),--(Expenditures!$C2:$C10000=5421),Expenditures!H2:H10000)

If you wanted to include the entire column, you could specify the range by
rows:
=SUMPRODUCT(--(Expenditures!$B1:$B65536="CB"),--(Expenditures!$C1:$C65536=5421),Expenditures!H1:H65536)

HTH,
Paul

 

[Ragdyer]

Your second example won't work in XL97!

If you try it, you'll see that XL replaces B1:B65535 with B:B, and returns a
#NUM! error.

Just remove one of the rows:
B2:B65536 or B1:B65535