sum using multiple criteria across 2 worksheets

[sweett00th]

I have a document with 2 worksheets (Summary & All Info). I want to
write a formula that can sum up a column only if it matches 2 criterias
on the summary sheet.

I have 2 criterias on the summary tab: cost center # and status
On the All info tab it has a column for cost center #, status, and
zeros and ones for each month to indicate if there is someone filling
this position. I need to add the ones and zeros up for each month
based on it matching the status and cost center #.

Summary Tab:
in cell A1
Cost Center #
in cell B1
status

these 2 need to both be true to sum it up on the All Info tab;

All info tab:
A1: cost center #
B1: status
C1: 1 or 0
c2: 1 or 0
c3: 1 or 0
Column C would need to be added up.

So A1 on the summary tab would need to match A1 on the All info tab
plus B1 would match B1 - and only then woudl it add up All column C
that matches.

Help!

[tobriant]

Try the sumif function.

[Bob Phillips]

=SUMPRODUCT(--(All!A!:A100=Summary!A1),--(All!B1:B100-Summary!B1),All!C1:C10
0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sweett00th" <[email protected]> wrote in message
news:[email protected]...
> I have a document with 2 worksheets (Summary & All Info). I want to
> write a formula that can sum up a column only if it matches 2 criterias
> on the summary sheet.
>
> I have 2 criterias on the summary tab: cost center # and status
> On the All info tab it has a column for cost center #, status, and
> zeros and ones for each month to indicate if there is someone filling
> this position. I need to add the ones and zeros up for each month
> based on it matching the status and cost center #.
>
> Summary Tab:
> in cell A1
> Cost Center #
> in cell B1
> status
>
> these 2 need to both be true to sum it up on the All Info tab;
>
> All info tab:
> A1: cost center #
> B1: status
> C1: 1 or 0
> c2: 1 or 0
> c3: 1 or 0
> Column C would need to be added up.
>
> So A1 on the summary tab would need to match A1 on the All info tab
> plus B1 would match B1 - and only then woudl it add up All column C
> that matches.
>
> Help!
>