SUMIF using two columns in both Range and Criteria

=SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)

--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!

Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
>

I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

As an aside, this version

=SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)

although longer is more efficient I am sure


--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> Bob Phillips wrote:
> > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> >
>
> I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

And might be safer, too.

If the OP had something like this,

Account Dept
7000 12
70001 2
7 00012

the original formula might yield incorrect results.

Bob Phillips wrote:
>
> As an aside, this version
>
> =SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)
>
> although longer is more efficient I am sure
>
> --
> HTH
>
> Bob Phillips
>
> "Gordon" <[email protected]> wrote in message
> news:[email protected]...
> > Bob Phillips wrote:
> > > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> > >
> >
> > I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

--

Dave Peterson

You can stay with SumIf if you create an additional column...

Let A1:C3 house the sample you provided.

D1: Concat

D2, copied down:

=A2&"#"&B2

Now invoke...

=SUMIF($D$2:$D$3,$F2&"#"&G$1,$C$2:$C$3)

where F2 houses an account number and G1 a department.

If you are on Excel 2003, convert A1:D3 into a list using
Data|List|Create List.

Gordon wrote:
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!

=SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)

--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!

Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
>

I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

As an aside, this version

=SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)

although longer is more efficient I am sure


--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> Bob Phillips wrote:
> > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> >
>
> I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

And might be safer, too.

If the OP had something like this,

Account Dept
7000 12
70001 2
7 00012

the original formula might yield incorrect results.

Bob Phillips wrote:
>
> As an aside, this version
>
> =SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)
>
> although longer is more efficient I am sure
>
> --
> HTH
>
> Bob Phillips
>
> "Gordon" <[email protected]> wrote in message
> news:[email protected]...
> > Bob Phillips wrote:
> > > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> > >
> >
> > I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

--

Dave Peterson

You can stay with SumIf if you create an additional column...

Let A1:C3 house the sample you provided.

D1: Concat

D2, copied down:

=A2&"#"&B2

Now invoke...

=SUMIF($D$2:$D$3,$F2&"#"&G$1,$C$2:$C$3)

where F2 houses an account number and G1 a department.

If you are on Excel 2003, convert A1:D3 into a list using
Data|List|Create List.

Gordon wrote:
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!

I have a speadsheet with columns as under:

Account Code Department Amount
------------ ---------- ------
7000 12 £100
7050 5 £250

There are a couple of hundred rows with different account numbers and a
variety of Department numbers.

Is there a way I can use SUMIF to summarize the amounts by Code AND
Department? In other words I want to sum ALL the amounts with Code 7000
and Dept 12 etc.

Thanks!

=SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)

--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!

Bob Phillips wrote:
> =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
>

I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

As an aside, this version

=SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)

although longer is more efficient I am sure


--
HTH

Bob Phillips

"Gordon" <[email protected]> wrote in message
news:[email protected]...
> Bob Phillips wrote:
> > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> >
>
> I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

And might be safer, too.

If the OP had something like this,

Account Dept
7000 12
70001 2
7 00012

the original formula might yield incorrect results.

Bob Phillips wrote:
>
> As an aside, this version
>
> =SUMPRODUCT(--(A1:A20=7000),--(B1:B20=12),C1:C20)
>
> although longer is more efficient I am sure
>
> --
> HTH
>
> Bob Phillips
>
> "Gordon" <[email protected]> wrote in message
> news:[email protected]...
> > Bob Phillips wrote:
> > > =SUMPRODUCT(--(A1:A20&B1:B20="700012"),C1:C20)
> > >
> >
> > I'd just found "sumproduct" but your version of it is MUCH neater. Thanks!

--

Dave Peterson

You can stay with SumIf if you create an additional column...

Let A1:C3 house the sample you provided.

D1: Concat

D2, copied down:

=A2&"#"&B2

Now invoke...

=SUMIF($D$2:$D$3,$F2&"#"&G$1,$C$2:$C$3)

where F2 houses an account number and G1 a department.

If you are on Excel 2003, convert A1:D3 into a list using
Data|List|Create List.

Gordon wrote:
> I have a speadsheet with columns as under:
>
> Account Code Department Amount
> ------------ ---------- ------
> 7000 12 £100
> 7050 5 £250
>
> There are a couple of hundred rows with different account numbers and a
> variety of Department numbers.
>
> Is there a way I can use SUMIF to summarize the amounts by Code AND
> Department? In other words I want to sum ALL the amounts with Code 7000
> and Dept 12 etc.
>
> Thanks!