Sumproduct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

googley - 01 Mar 2008 02:51 GMT

Currently using sum product to total number of all components with RS
in column E2:E with the following formula
=SUMPRODUCT((LEN('SAP Dump'!$E$2:$E$64587)=17)*(--
(ISNUMBER(FIND("RS",'SAP Dump'!$E$2:$E$64587))))) In my example below
this would return 3

Now I need to sum the quantity with "PLANNED" in Column A2:A64587

A
E
LinType
Components
PLANNED
2070348-RS119-002
STOCK
2070348-RS119-002
SHIPPED
2070348-RS119-002

Reply to this Message

Barb Reinhardt - 01 Mar 2008 03:10 GMT

I thnk this is what you're asking

=SUMPRODUCT(--(A2:A64587="PLANNED" ))

gives you the number of times PLANNED is entered.

Signature

HTH,
Barb Reinhardt

> Currently using sum product to total number of all components with RS
> in column E2:E with the following formula
[quoted text clipped - 14 lines]
> SHIPPED
> 2070348-RS119-002

Reply to this Message

googley - 01 Mar 2008 03:30 GMT

On Feb 29, 10:10 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:

> I thnk this is what you're asking
>
[quoted text clipped - 26 lines]
>
> - Show quoted text -

Actually I am calculating the quantity of the 2070348-RS119-002
components that are listed in column a as planned

Reply to this Message

Don Guillett - 01 Mar 2008 12:14 GMT

Just add another parameter such as
=sumproduct((a2:a22="planned")*(e2:e22=b1)) where b1 contains your
2070348-RS119-002

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

On Feb 29, 10:10 pm, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:

> I thnk this is what you're asking
>
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Actually I am calculating the quantity of the 2070348-RS119-002
components that are listed in column a as planned

Reply to this Message

Rate this thread: