sumif function

[AHANG JJJ]

What is the syntax for sumif function with two criteria range and two criteria?

[RagDyer]

Use a different function ... SumProduct().

=SumProduct((A1:A100=criteria1)*(B1:B100=criteria2)*C1:C100)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"AHANG JJJ" <AHANG [email protected]> wrote in message
news:[email protected]...
> What is the syntax for sumif function with two criteria range and two
criteria?

[Myrna Larson]

SUMIF and COUNTIF allow for only one criterion. You need array formulas or
SUMPRODUCT if you have multiple criteria. See the other response for one way.

On Fri, 30 Sep 2005 19:11:16 -0700, AHANG JJJ <AHANG
[email protected]> wrote:

>What is the syntax for sumif function with two criteria range and two
criteria?

[oomyoo]

I have something like this:

A B C D

jim 4 jim 2
cathy 2 cathy 4
mary 5 mary 3

I would like to sum the values in B and D that corresponds to jim. I tried
=sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work. Any
help would be much appreciated.

Thanks!




"RagDyer" wrote:

> Use a different function ... SumProduct().
>
> =SumProduct((A1:A100=criteria1)*(B1:B100=criteria2)*C1:C100)
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "AHANG JJJ" <AHANG [email protected]> wrote in message
> news:[email protected]...
> > What is the syntax for sumif function with two criteria range and two
> criteria?
>
>

[Domenic]

Try...

=SUMIF(A1:C3,"Jim",B1:D3)

Hope this helps!

In article <[email protected]>,
oomyoo <[email protected]> wrote:

> I have something like this:
>
> A B C D
>
> jim 4 jim 2
> cathy 2 cathy 4
> mary 5 mary 3
>
> I would like to sum the values in B and D that corresponds to jim. I tried
> =sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work. Any
> help would be much appreciated.
>
> Thanks!

[oomyoo]

nope, didn't work...

I should get a value of 6, but I'm only getting 4. Any other ideas?

"Domenic" wrote:

> Try...
>
> =SUMIF(A1:C3,"Jim",B1:D3)
>
> Hope this helps!
>
> In article <[email protected]>,
> oomyoo <[email protected]> wrote:
>
> > I have something like this:
> >
> > A B C D
> >
> > jim 4 jim 2
> > cathy 2 cathy 4
> > mary 5 mary 3
> >
> > I would like to sum the values in B and D that corresponds to jim. I tried
> > =sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work. Any
> > help would be much appreciated.
> >
> > Thanks!
>

[Bernie Deitrick]

=SUMIF(A1:A3,"Jim",B1:B3) + SUMIF(C1:C3,"Jim",D1:D3)

HTH,
Bernie
MS Excel MVP


"oomyoo" <[email protected]> wrote in message
news:[email protected]...
> nope, didn't work...
>
> I should get a value of 6, but I'm only getting 4. Any other ideas?
>
> "Domenic" wrote:
>
>> Try...
>>
>> =SUMIF(A1:C3,"Jim",B1:D3)
>>
>> Hope this helps!
>>
>> In article <[email protected]>,
>> oomyoo <[email protected]> wrote:
>>
>> > I have something like this:
>> >
>> > A B C D
>> >
>> > jim 4 jim 2
>> > cathy 2 cathy 4
>> > mary 5 mary 3
>> >
>> > I would like to sum the values in B and D that corresponds to jim. I tried
>> > =sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work. Any
>> > help would be much appreciated.
>> >
>> > Thanks!
>>

[oomyoo]

i was thinking complicated instead of simple... thanks!

"Bernie Deitrick" wrote:

> =SUMIF(A1:A3,"Jim",B1:B3) + SUMIF(C1:C3,"Jim",D1:D3)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "oomyoo" <[email protected]> wrote in message
> news:[email protected]...
> > nope, didn't work...
> >
> > I should get a value of 6, but I'm only getting 4. Any other ideas?
> >
> > "Domenic" wrote:
> >
> >> Try...
> >>
> >> =SUMIF(A1:C3,"Jim",B1:D3)
> >>
> >> Hope this helps!
> >>
> >> In article <[email protected]>,
> >> oomyoo <[email protected]> wrote:
> >>
> >> > I have something like this:
> >> >
> >> > A B C D
> >> >
> >> > jim 4 jim 2
> >> > cathy 2 cathy 4
> >> > mary 5 mary 3
> >> >
> >> > I would like to sum the values in B and D that corresponds to jim. I tried
> >> > =sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work. Any
> >> > help would be much appreciated.
> >> >
> >> > Thanks!
> >>
>
>
>

[Domenic]

The formula definitely returns the correct amount. Can you post the
exact formula you're using?

In article <[email protected]>,
oomyoo <[email protected]> wrote:

> nope, didn't work...
>
> I should get a value of 6, but I'm only getting 4. Any other ideas?
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMIF(A1:C3,"Jim",B1:D3)
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > oomyoo <[email protected]> wrote:
> >
> > > I have something like this:
> > >
> > > A B C D
> > >
> > > jim 4 jim 2
> > > cathy 2 cathy 4
> > > mary 5 mary 3
> > >
> > > I would like to sum the values in B and D that corresponds to jim. I
> > > tried
> > > =sumproduct((criteria1)*(criteria2)*(range B,D)), but this doesn't work.
> > > Any
> > > help would be much appreciated.
> > >
> > > Thanks!
> >