Adding based on Multiple Criteria?
 

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")*(Expenditu res!$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

Adding based on Multiple Criteria?
 

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

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:H6 5536)

HTH,
Paul

"Audra" wrote in message
...
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")*(Expenditu res!$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

Adding based on Multiple Criteria?
 

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PCLIVE" wrote in message
...
Though you still have to specify the range, you can use:
=SUMPRODUCT(--(Expenditures!$B2:$B10000="CB"),--(Expenditures!$C2:$C10000=5421),Expenditures!H2:H1 0000)

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:H6 5536)

HTH,
Paul

"Audra" wrote in message
...
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")*(Expenditu res!$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