software

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

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

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

--
Mark

=sumproduct(--(left(f1:F100,3)=quot;nptquot;),--(left(a1:a100,2)=quot;pcquot;))

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

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

--

Dave Peterson


hi!

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

-via135

Mark Wrote:
gt; Can someone assist me with a formula to sumproduct with wildcards,
gt; please?
gt;
gt; =SUMPRODUCT(--(F:F=quot;NPT*quot;),--(A:A=quot;PC*quot;))
gt;
gt; I've got this but it doesn't work!!
gt;
gt; --
gt; Mark--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=521504Thanks Dave, but I'm getting #N/A and I can see there are some rows with this
info!

Can you offer any suggestion?

--
Markquot;Dave Petersonquot; wrote:

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

What formula did you use?

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

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

--

Dave Peterson

Dave,

Brilliant!

It was the #N/A in column F!

Sorted it, many thanks.
--
Markquot;Dave Petersonquot; wrote:

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