=?Utf-8?Q?VBA_=E2=80=93_Problem_with_compound_SUMI?==?Utf-8?Q?F_function?=

gary · 05-11-2006, 06:20 PM

I am trying to select criteria from two columns and then do a sum on two
other columns. The first SUMIF works fine (with any single criteria) but I
am unable to add the second criteria. When I try, I get a “type mismatch”
error.

This works:
Total = SUMIF (Application.SUMIF (Range (“L4:L500)", ItemEmp, Range
("M4:N500”))

This does not work:

Total = SUMIF (Application.SUMIF ( (Range (“L4:L500") =ItemEmp) * (Range
(“D4:D500") =ItemColor), Range ("M4:N500”))

Thanks,

gary

Jim Thomlinson · 05-11-2006, 07:15 PM

You probably want to use a sumpriduct formula... Take a look at this website...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson

"gary" wrote:

> I am trying to select criteria from two columns and then do a sum on two
> other columns. The first SUMIF works fine (with any single criteria) but I
> am unable to add the second criteria. When I try, I get a “type mismatch”
> error.
>
> This works:
> Total = SUMIF (Application.SUMIF (Range (“L4:L500)", ItemEmp, Range
> ("M4:N500”))
>
> This does not work:
>
> Total = SUMIF (Application.SUMIF ( (Range (“L4:L500") =ItemEmp) * (Range
> (“D4:D500") =ItemColor), Range ("M4:N500”))
>
> Thanks,
>
> gary

Bob Phillips · 05-11-2006, 07:15 PM

Total = ActiveSheet.Evaluate("SUMPRODUCT(--(L4:L500=""" & ItemEmp & """)" &
_
"--(D4:D500=""" & ItemColor & """),M4:N500)")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:E6392417-321D-489E-BDBF-502157440A7F@microsoft.com...
> I am trying to select criteria from two columns and then do a sum on two
> other columns. The first SUMIF works fine (with any single criteria) but
I
> am unable to add the second criteria. When I try, I get a "type mismatch"
> error.
>
> This works:
> Total = SUMIF (Application.SUMIF (Range ("L4:L500)", ItemEmp, Range
> ("M4:N500"))
>
> This does not work:
>
> Total = SUMIF (Application.SUMIF ( (Range ("L4:L500") =ItemEmp) * (Range
> ("D4:D500") =ItemColor), Range ("M4:N500"))
>
> Thanks,
>
> gary

gary · 05-11-2006, 08:25 PM

Bob,

When I try this I get "error = 2015"

Any ideas ?

"Bob Phillips" wrote:

> Total = ActiveSheet.Evaluate("SUMPRODUCT(--(L4:L500=""" & ItemEmp & """)" &
> _
> "--(D4:D500=""" & ItemColor & """),M4:N500)")
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "gary" <gary@discussions.microsoft.com> wrote in message
> news:E6392417-321D-489E-BDBF-502157440A7F@microsoft.com...
> > I am trying to select criteria from two columns and then do a sum on two
> > other columns. The first SUMIF works fine (with any single criteria) but
> I
> > am unable to add the second criteria. When I try, I get a "type mismatch"
> > error.
> >
> > This works:
> > Total = SUMIF (Application.SUMIF (Range ("L4:L500)", ItemEmp, Range
> > ("M4:N500"))
> >
> > This does not work:
> >
> > Total = SUMIF (Application.SUMIF ( (Range ("L4:L500") =ItemEmp) * (Range
> > ("D4:D500") =ItemColor), Range ("M4:N500"))
> >
> > Thanks,
> >
> > gary
>
>
>

Bob Phillips · 05-12-2006, 06:15 AM

Hi Gary,

Sorry I didn't spot that there were two columns being added. In that
instance, the -- doesn't work, you need *

Total = ActiveSheet.Evaluate( _
"SUMPRODUCT((L4:L500=""" & ItemEmp & _
""")*(D4:D500=""" & ItemColor & _
""")*(M4:N500))")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:16DABD71-8EA0-4855-A165-527217CD0DB5@microsoft.com...
> Bob,
>
> When I try this I get "error = 2015"
>
> Any ideas ?
>
>
> "Bob Phillips" wrote:
>
> > Total = ActiveSheet.Evaluate("SUMPRODUCT(--(L4:L500=""" & ItemEmp &
""")" &
> > _
> > "--(D4:D500=""" & ItemColor &
"""),M4:N500)")
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "gary" <gary@discussions.microsoft.com> wrote in message
> > news:E6392417-321D-489E-BDBF-502157440A7F@microsoft.com...
> > > I am trying to select criteria from two columns and then do a sum on
two
> > > other columns. The first SUMIF works fine (with any single criteria)
but
> > I
> > > am unable to add the second criteria. When I try, I get a "type
mismatch"
> > > error.
> > >
> > > This works:
> > > Total = SUMIF (Application.SUMIF (Range ("L4:L500)", ItemEmp, Range
> > > ("M4:N500"))
> > >
> > > This does not work:
> > >
> > > Total = SUMIF (Application.SUMIF ( (Range ("L4:L500") =ItemEmp) *
(Range
> > > ("D4:D500") =ItemColor), Range ("M4:N500"))
> > >
> > > Thanks,
> > >
> > > gary
> >
> >
> >

gary · 05-12-2006, 07:10 AM

Hi Bob,

I guess I'm getting closer. The 2015 error is gone, but now I always get a
total = 0 which isn't the correct sum.

I'm wondering if the three quotes are waht I really need.

I hope you have another idea.

Thanks,

Gary

"Bob Phillips" wrote:

> Hi Gary,
>
> Sorry I didn't spot that there were two columns being added. In that
> instance, the -- doesn't work, you need *
>
>
> Total = ActiveSheet.Evaluate( _
> "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> """)*(D4:D500=""" & ItemColor & _
> """)*(M4:N500))")
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "gary" <gary@discussions.microsoft.com> wrote in message
> news:16DABD71-8EA0-4855-A165-527217CD0DB5@microsoft.com...
> > Bob,
> >
> > When I try this I get "error = 2015"
> >
> > Any ideas ?
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Total = ActiveSheet.Evaluate("SUMPRODUCT(--(L4:L500=""" & ItemEmp &
> """)" &
> > > _
> > > "--(D4:D500=""" & ItemColor &
> """),M4:N500)")
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "gary" <gary@discussions.microsoft.com> wrote in message
> > > news:E6392417-321D-489E-BDBF-502157440A7F@microsoft.com...
> > > > I am trying to select criteria from two columns and then do a sum on
> two
> > > > other columns. The first SUMIF works fine (with any single criteria)
> but
> > > I
> > > > am unable to add the second criteria. When I try, I get a "type
> mismatch"
> > > > error.
> > > >
> > > > This works:
> > > > Total = SUMIF (Application.SUMIF (Range ("L4:L500)", ItemEmp, Range
> > > > ("M4:N500"))
> > > >
> > > > This does not work:
> > > >
> > > > Total = SUMIF (Application.SUMIF ( (Range ("L4:L500") =ItemEmp) *
> (Range
> > > > ("D4:D500") =ItemColor), Range ("M4:N500"))
> > > >
> > > > Thanks,
> > > >
> > > > gary
> > >
> > >
> > >
>
>
>

Bob Phillips · 05-12-2006, 07:15 AM

Gary,

Are ItemEmp and ItemColor text or numeric variables?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:BA165017-3AD0-4C88-852C-BA4897ECFC85@microsoft.com...
> Hi Bob,
>
> I guess I'm getting closer. The 2015 error is gone, but now I always get
a
> total = 0 which isn't the correct sum.
>
> I'm wondering if the three quotes are waht I really need.
>
> I hope you have another idea.
>
> Thanks,
>
> Gary
>
> "Bob Phillips" wrote:
>
> > Hi Gary,
> >
> > Sorry I didn't spot that there were two columns being added. In that
> > instance, the -- doesn't work, you need *
> >
> >
> > Total = ActiveSheet.Evaluate( _
> > "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> > """)*(D4:D500=""" & ItemColor & _
> > """)*(M4:N500))")
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "gary" <gary@discussions.microsoft.com> wrote in message
> > news:16DABD71-8EA0-4855-A165-527217CD0DB5@microsoft.com...
> > > Bob,
> > >
> > > When I try this I get "error = 2015"
> > >
> > > Any ideas ?
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Total = ActiveSheet.Evaluate("SUMPRODUCT(--(L4:L500=""" & ItemEmp &
> > """)" &
> > > > _
> > > > "--(D4:D500=""" & ItemColor &
> > """),M4:N500)")
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove xxx from email address if mailing direct)
> > > >
> > > > "gary" <gary@discussions.microsoft.com> wrote in message
> > > > news:E6392417-321D-489E-BDBF-502157440A7F@microsoft.com...
> > > > > I am trying to select criteria from two columns and then do a sum
on
> > two
> > > > > other columns. The first SUMIF works fine (with any single
criteria)
> > but
> > > > I
> > > > > am unable to add the second criteria. When I try, I get a "type
> > mismatch"
> > > > > error.
> > > > >
> > > > > This works:
> > > > > Total = SUMIF (Application.SUMIF (Range ("L4:L500)", ItemEmp,
Range
> > > > > ("M4:N500"))
> > > > >
> > > > > This does not work:
> > > > >
> > > > > Total = SUMIF (Application.SUMIF ( (Range ("L4:L500") =ItemEmp) *
> > (Range
> > > > > ("D4:D500") =ItemColor), Range ("M4:N500"))
> > > > >
> > > > > Thanks,
> > > > >
> > > > > gary
> > > >
> > > >
> > > >
> >
> >
> >