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

[gary]

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]

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]

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]

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]

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]

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]

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
> > > >
> > > >
> > > >
> >
> >
> >

[gary]

Hio Bob,

ItemEmp is text string and ItemColor is numeric. Columns M and N are the
numeric columns that I want to sum for the qualifing name and color.

Gary

"Bob Phillips" wrote:

> 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Okay, what you need then is

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

I tested with both as text :-(

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> Hio Bob,
>
> ItemEmp is text string and ItemColor is numeric. Columns M and N are the
> numeric columns that I want to sum for the qualifing name and color.
>
> Gary
>
> "Bob Phillips" wrote:
>
> > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[gary]

Bob,

I guess I's still doing something wrong. I am now getting Type mismatch
errors. Is it possible to get a soft copy of your test file or for me to
send a soft copy of mine ? Or can I send snaphot of debuggeer output ?

I'm not sure what to do next.

Thanks for your patience,

Gary

"Bob Phillips" wrote:

> Okay, what you need then is
>
> Total = ActiveSheet.Evaluate( _
> "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> """)*(D4:D500=" & ItemColor & _
> ")*(M4:N500))")
>
> I tested with both as text :-(
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "gary" <gary@discussions.microsoft.com> wrote in message
> news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> > Hio Bob,
> >
> > ItemEmp is text string and ItemColor is numeric. Columns M and N are the
> > numeric columns that I want to sum for the qualifing name and color.
> >
> > Gary
> >
> > "Bob Phillips" wrote:
> >
> > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Gary,

Just send me your file and some details of the values.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:C74393FA-1578-49AC-97D6-9D0B9E7FBEB4@microsoft.com...
> Bob,
>
> I guess I's still doing something wrong. I am now getting Type mismatch
> errors. Is it possible to get a soft copy of your test file or for me to
> send a soft copy of mine ? Or can I send snaphot of debuggeer output ?
>
> I'm not sure what to do next.
>
> Thanks for your patience,
>
> Gary
>
> "Bob Phillips" wrote:
>
> > Okay, what you need then is
> >
> > Total = ActiveSheet.Evaluate( _
> > "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> > """)*(D4:D500=" & ItemColor & _
> > ")*(M4:N500))")
> >
> > I tested with both as text :-(
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "gary" <gary@discussions.microsoft.com> wrote in message
> > news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> > > Hio Bob,
> > >
> > > ItemEmp is text string and ItemColor is numeric. Columns M and N are
the
> > > numeric columns that I want to sum for the qualifing name and color.
> > >
> > > Gary
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[gary]

Hi Bob,

I tried to send you an email yesterday with the file without success. How
do I send an attachment ? Or what email address should I use.

Thanks,

Gary

"Bob Phillips" wrote:

> Gary,
>
> Just send me your file and some details of the values.
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "gary" <gary@discussions.microsoft.com> wrote in message
> news:C74393FA-1578-49AC-97D6-9D0B9E7FBEB4@microsoft.com...
> > Bob,
> >
> > I guess I's still doing something wrong. I am now getting Type mismatch
> > errors. Is it possible to get a soft copy of your test file or for me to
> > send a soft copy of mine ? Or can I send snaphot of debuggeer output ?
> >
> > I'm not sure what to do next.
> >
> > Thanks for your patience,
> >
> > Gary
> >
> > "Bob Phillips" wrote:
> >
> > > Okay, what you need then is
> > >
> > > Total = ActiveSheet.Evaluate( _
> > > "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> > > """)*(D4:D500=" & ItemColor & _
> > > ")*(M4:N500))")
> > >
> > > I tested with both as text :-(
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "gary" <gary@discussions.microsoft.com> wrote in message
> > > news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> > > > Hio Bob,
> > > >
> > > > ItemEmp is text string and ItemColor is numeric. Columns M and N are
> the
> > > > numeric columns that I want to sum for the qualifing name and color.
> > > >
> > > > Gary
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[gary]

Bob,

I re-re checked my work and found that indeed you had it right !! For
text, single quotes are used and for numerics, double quotes must be used on
both the criteria and value.

Thanks fro your help.

I belvie this problem is solved now.

Gary

"Bob Phillips" wrote:

> Gary,
>
> Just send me your file and some details of the values.
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "gary" <gary@discussions.microsoft.com> wrote in message
> news:C74393FA-1578-49AC-97D6-9D0B9E7FBEB4@microsoft.com...
> > Bob,
> >
> > I guess I's still doing something wrong. I am now getting Type mismatch
> > errors. Is it possible to get a soft copy of your test file or for me to
> > send a soft copy of mine ? Or can I send snaphot of debuggeer output ?
> >
> > I'm not sure what to do next.
> >
> > Thanks for your patience,
> >
> > Gary
> >
> > "Bob Phillips" wrote:
> >
> > > Okay, what you need then is
> > >
> > > Total = ActiveSheet.Evaluate( _
> > > "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> > > """)*(D4:D500=" & ItemColor & _
> > > ")*(M4:N500))")
> > >
> > > I tested with both as text :-(
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "gary" <gary@discussions.microsoft.com> wrote in message
> > > news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> > > > Hio Bob,
> > > >
> > > > ItemEmp is text string and ItemColor is numeric. Columns M and N are
> the
> > > > numeric columns that I want to sum for the qualifing name and color.
> > > >
> > > > Gary
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Other way around.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"gary" <gary@discussions.microsoft.com> wrote in message
news:A77CE8DB-10A6-4308-A9C0-F386B003C9E3@microsoft.com...
> Bob,
>
> I re-re checked my work and found that indeed you had it right !! For
> text, single quotes are used and for numerics, double quotes must be used
on
> both the criteria and value.
>
> Thanks fro your help.
>
> I belvie this problem is solved now.
>
> Gary
>
> "Bob Phillips" wrote:
>
> > Gary,
> >
> > Just send me your file and some details of the values.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "gary" <gary@discussions.microsoft.com> wrote in message
> > news:C74393FA-1578-49AC-97D6-9D0B9E7FBEB4@microsoft.com...
> > > Bob,
> > >
> > > I guess I's still doing something wrong. I am now getting Type
mismatch
> > > errors. Is it possible to get a soft copy of your test file or for me
to
> > > send a soft copy of mine ? Or can I send snaphot of debuggeer output
?
> > >
> > > I'm not sure what to do next.
> > >
> > > Thanks for your patience,
> > >
> > > Gary
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Okay, what you need then is
> > > >
> > > > Total = ActiveSheet.Evaluate( _
> > > > "SUMPRODUCT((L4:L500=""" & ItemEmp & _
> > > > """)*(D4:D500=" & ItemColor & _
> > > > ")*(M4:N500))")
> > > >
> > > > I tested with both as text :-(
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove xxx from email address if mailing direct)
> > > >
> > > > "gary" <gary@discussions.microsoft.com> wrote in message
> > > > news:9560A55F-C616-4706-B003-952807EEC764@microsoft.com...
> > > > > Hio Bob,
> > > > >
> > > > > ItemEmp is text string and ItemColor is numeric. Columns M and N
are
> > the
> > > > > numeric columns that I want to sum for the qualifing name and
color.
> > > > >
> > > > > Gary
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >