SUMIF Formula w/ OR Criteria

I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
would like to include an OR criteria
=SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
value of zero. The first formula above yields the correct answer of "7500".
Is my syntax incorrect? Thank you in advance for your assistance.

One way:

=SUM(SUMIF(B5:B21,{"partner","manager"},D5:D21))



SJT wrote:
>
> I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
> would like to include an OR criteria
> =SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
> value of zero. The first formula above yields the correct answer of "7500".
> Is my syntax incorrect? Thank you in advance for your assistance.

--

Dave Peterson

You could simply use
=SUMIF(B5:B21,"partner",D5:D21)+SUMIF(B5:B21,"manager",D5:D21)
But I reckon there may be another way.......anyone?

Rob


"SJT" <[email protected]> wrote in message
news:[email protected]...
>I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
> would like to include an OR criteria
> =SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
> value of zero. The first formula above yields the correct answer of
> "7500".
> Is my syntax incorrect? Thank you in advance for your assistance.

Thanks. What is the function that the "{" serve in your formula?

"Dave Peterson" wrote:

> One way:
>
> =SUM(SUMIF(B5:B21,{"partner","manager"},D5:D21))
>
>
>
> SJT wrote:
> >
> > I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
> > would like to include an OR criteria
> > =SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
> > value of zero. The first formula above yields the correct answer of "7500".
> > Is my syntax incorrect? Thank you in advance for your assistance.
>
> --
>
> Dave Peterson
>

It tells excel to treat that as an array.

Put some test data in A1:A2
put this in A3: =a1:a2
select A3, hit F2 and then hit F9.

Look at what you see in the formula bar.



SJT wrote:
>
> Thanks. What is the function that the "{" serve in your formula?
>
> "Dave Peterson" wrote:
>
> > One way:
> >
> > =SUM(SUMIF(B5:B21,{"partner","manager"},D5:D21))
> >
> >
> >
> > SJT wrote:
> > >
> > > I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
> > > would like to include an OR criteria
> > > =SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
> > > value of zero. The first formula above yields the correct answer of "7500".
> > > Is my syntax incorrect? Thank you in advance for your assistance.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks for all the help.

"Dave Peterson" wrote:

> It tells excel to treat that as an array.
>
> Put some test data in A1:A2
> put this in A3: =a1:a2
> select A3, hit F2 and then hit F9.
>
> Look at what you see in the formula bar.
>
>
>
> SJT wrote:
> >
> > Thanks. What is the function that the "{" serve in your formula?
> >
> > "Dave Peterson" wrote:
> >
> > > One way:
> > >
> > > =SUM(SUMIF(B5:B21,{"partner","manager"},D5:D21))
> > >
> > >
> > >
> > > SJT wrote:
> > > >
> > > > I have written the following formula =SUMIF(B5:B21,"partner",D5:D21) but
> > > > would like to include an OR criteria
> > > > =SUMIF(B5:B21,OR("partner","manager"),D5:D21) but it gives me an incorrect
> > > > value of zero. The first formula above yields the correct answer of "7500".
> > > > Is my syntax incorrect? Thank you in advance for your assistance.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>