sumproduct with wildcard

[Mark]

Can someone assist me with a formula to sumproduct with wildcards, please?

=SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))

I've got this but it doesn't work!!

--
Mark

[Dave Peterson]

=sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc"))

You can't use whole columns with this kind of formula.

Mark wrote:
>
> Can someone assist me with a formula to sumproduct with wildcards, please?
>
> =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))
>
> I've got this but it doesn't work!!
>
> --
> Mark

--

Dave Peterson

[Mark]

Thanks Dave, but I'm getting #N/A and I can see there are some rows with this
info!

Can you offer any suggestion?

--
Mark


"Dave Peterson" wrote:

> =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc"))
>
> You can't use whole columns with this kind of formula.
>
> Mark wrote:
> >
> > Can someone assist me with a formula to sumproduct with wildcards, please?
> >
> > =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))
> >
> > I've got this but it doesn't work!!
> >
> > --
> > Mark
>
> --
>
> Dave Peterson
>

[Dave Peterson]

What formula did you use?

Do you have #n/a's in either of those two ranges?

Mark wrote:
>
> Thanks Dave, but I'm getting #N/A and I can see there are some rows with this
> info!
>
> Can you offer any suggestion?
>
> --
> Mark
>
> "Dave Peterson" wrote:
>
> > =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc"))
> >
> > You can't use whole columns with this kind of formula.
> >
> > Mark wrote:
> > >
> > > Can someone assist me with a formula to sumproduct with wildcards, please?
> > >
> > > =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))
> > >
> > > I've got this but it doesn't work!!
> > >
> > > --
> > > Mark
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Mark]

Dave,

Brilliant!

It was the #N/A in column F!

Sorted it, many thanks.
--
Mark


"Dave Peterson" wrote:

> What formula did you use?
>
> Do you have #n/a's in either of those two ranges?
>
> Mark wrote:
> >
> > Thanks Dave, but I'm getting #N/A and I can see there are some rows with this
> > info!
> >
> > Can you offer any suggestion?
> >
> > --
> > Mark
> >
> > "Dave Peterson" wrote:
> >
> > > =sumproduct(--(left(f1:F100,3)="npt"),--(left(a1:a100,2)="pc"))
> > >
> > > You can't use whole columns with this kind of formula.
> > >
> > > Mark wrote:
> > > >
> > > > Can someone assist me with a formula to sumproduct with wildcards, please?
> > > >
> > > > =SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))
> > > >
> > > > I've got this but it doesn't work!!
> > > >
> > > > --
> > > > Mark
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[via135]

hi!

SUMIF can use wildcards, but only for one test, but SUMPRODUCT doesn't support wildcards directly.

-via135

Can someone assist me with a formula to sumproduct with wildcards, please?

=SUMPRODUCT(--(F:F="NPT*"),--(A:A="PC*"))

I've got this but it doesn't work!!

--
Mark