SUMPRODUCT with multiple criteria
 

Hi,

Perhaps I've misunderstood but maybe this

=SUMPRODUCT((L2:L33331=B7)*(L2:L33331<=B9)*(LEFT( D2:D33331,9)="EQUIPMENT")*(LEFT(P2:P33331,8)="COMP LETE"))

Mike

On Thu, 22 Jan 2009 12:25:56 -0800 (PST), wrote:

Hi Group,
I am having trouble with a sumproduct formula.
=SUMPRODUCT(--(L2:L33331=B7),--(L2:L33331<=B9),--
(D2:D33331="EQUIPMENT")*OR
(P2:P33331="COMPLETE1",P2:P33331="COMPLETE2",P2:P 33331="COMPLETE3",P2:P33331="COMPLETE4"))
B7 and B9 are my inclusive dates, checked against Col L, and I want to
count the number of "EQUIPMENT" that are any of the 4 "COMPLETES".
This works very well if I only have 1 type of "EQUIPMENT", but even
then I'm not certain if that's the correct way to do that. My problem
arises when I want more than 1 "EQUIPMENT" , such as
"EQUIPMENT1","EQUIPMENT2","EQUIPMENT3","EQUIPMENT 4". Is there a way to
do what I'm asking? Any help will be very much appreciated!
Ken

SUMPRODUCT with multiple criteria
 

Hi,

The mod to the last part of your formula does exactly that. Because
the first 8 letters pf EQUIPMENTn are all the same the line
left(range,8)="EQUIPMENT"
catches them all and removes the need for the OR part of your formula

I think you need to post a small sample of data along with the result
you 'expect' from any formula

Mike

On Thu, 22 Jan 2009 12:51:42 -0800 (PST), wrote:

I should have clarified. What I need to is: "EQUIPMENT1", or
"EQUIPMENT2", or "EQUIPMENT3", or "EQUIPMENT4" that has one of the 4
"COMPLETES" that fall in the date range I specify in B7 and B9. As I
mentioned before, the formula works well with just "EQUIPMENT".
Thanks for your help!
Ken

SUMPRODUCT with multiple criteria
 

Hi Group,
I am having trouble with a sumproduct formula.
=SUMPRODUCT(--(L2:L33331=B7),--(L2:L33331<=B9),--
(D2:D33331="EQUIPMENT")*OR
(P2:P33331="COMPLETE1",P2:P33331="COMPLETE2",P2:P3 3331="COMPLETE3",P2:P33331="COMPLETE4"))
B7 and B9 are my inclusive dates, checked against Col L, and I want to
count the number of "EQUIPMENT" that are any of the 4 "COMPLETES".
This works very well if I only have 1 type of "EQUIPMENT", but even
then I'm not certain if that's the correct way to do that. My problem
arises when I want more than 1 "EQUIPMENT" , such as
"EQUIPMENT1","EQUIPMENT2","EQUIPMENT3","EQUIPMENT4 ". Is there a way to
do what I'm asking? Any help will be very much appreciated!
Ken

SUMPRODUCT with multiple criteria
 

I should have clarified. What I need to is: "EQUIPMENT1", or
"EQUIPMENT2", or "EQUIPMENT3", or "EQUIPMENT4" that has one of the 4
"COMPLETES" that fall in the date range I specify in B7 and B9. As I
mentioned before, the formula works well with just "EQUIPMENT".
Thanks for your help!
Ken

SUMPRODUCT with multiple criteria
 

And I also should have said that "EQUIPMENT" was for illustration. The
actual value could be "APPLES" or "ORANGES" or "PEARS" or "PEACHES" or
just 4 different types of anything. The second part of your formula
would probably work as there are 4 different conditions of "COMPLETE",
but there are 4 different names for the first part. Have I muddied the
waters? My apologies for the confusion.
Ken

Jan 22, 3:51*pm, wrote:
I should have clarified. What I need to is: "EQUIPMENT1", or
"EQUIPMENT2", or "EQUIPMENT3", or "EQUIPMENT4" that has one of the 4
"COMPLETES" that fall in the date range I specify in B7 and B9. As I
mentioned before, the formula works well with just "EQUIPMENT".
Thanks for your help!
Ken

SUMPRODUCT with multiple criteria
 

Mike, here is the actual formula with the last part of the formula
that you provided to me, and it does indeed work for the completes! I
just want to add 3 more pieces of equipment to the first part where
"BY-PASS CAPSULE" is now.

=SUMPRODUCT(--(JobLogEntry!L2:L33331=B7),--(JobLogEntry!
L2:L33331<=B9),--(JobLogEntry!D2:D33331="BY-PASS CAPSULE")*(LEFT
(JobLogEntry!P2:P33331,8)="COMPLETE"))

SUMPRODUCT with multiple criteria
 

Try it this way:

=SUMPRODUCT(--(JobLogEntry!L2:L33331=B7),--(JobLogEntry!
L2:L33331<=B9),--(JobLogEntry!D2:D33331={"BY-PASS
CAPSULE","apples","oranges","pears"}),--(LEFT(JobLogEntry!P2:P33331,8)
="COMPLETE"))

Obviously, change apples, oranges and pears within the curly braces to
your actual equipment names.

Hope this helps.

Pete

On Jan 22, 9:27*pm, wrote:
Mike, here is the actual formula with the last part of the formula
that you provided to me, and it does indeed work for the completes! I
just want to add 3 more pieces of equipment to the first part where
"BY-PASS CAPSULE" *is now.

=SUMPRODUCT(--(JobLogEntry!L2:L33331=B7),--(JobLogEntry!
L2:L33331<=B9),--(JobLogEntry!D2:D33331="BY-PASS CAPSULE")*(LEFT
(JobLogEntry!P2:P33331,8)="COMPLETE"))

SUMPRODUCT with multiple criteria
 

List your variables in a range of cells:

A1 = BY-PASS CAPSULE
A2 = variable2
A3 = variable3
A4 = variable4

A5 = COMPLETE

Then: (I'm leaving out the sheet name)

=SUMPRODUCT(--(ISNUMBER(MATCH(D2:D33331,A1:A4,0))),--(L2:L33331=B7),--(L2:L33331<=B9),--(LEFT(P2:P33331,8)=A5))

--
Biff
Microsoft Excel MVP


wrote in message
...
Mike, here is the actual formula with the last part of the formula
that you provided to me, and it does indeed work for the completes! I
just want to add 3 more pieces of equipment to the first part where
"BY-PASS CAPSULE" is now.

=SUMPRODUCT(--(JobLogEntry!L2:L33331=B7),--(JobLogEntry!
L2:L33331<=B9),--(JobLogEntry!D2:D33331="BY-PASS CAPSULE")*(LEFT
(JobLogEntry!P2:P33331,8)="COMPLETE"))

SUMPRODUCT with multiple criteria
 

Biff and Pete,
Thanks guys for helping me....I have tried both ways, but I can't
get either way to work....I can't see what the problem is here that
stops it from working. Biff's example:

=SUMPRODUCT(--(ISNUMBER(MATCH(JobLogEntry!D2:D33331,A22:A25,0))) ,--
(JobLogEntry!L2:L33331=B7),--(JobLogEntry!L2:*L33331<=B9),--(LEFT
(JobLogEntry!P2:P33331,8)=A25))

Pete I tried your formula it wouldn't work with more than piece of
equipment.
?????Puzzled!!
Ken

SUMPRODUCT with multiple criteria
 

I think in your interpretation of Biff's formula your final A25 should
be A26. I've assumed that you have used the 4 cells A22:A25 to list
your four names, so you need to use A26 for COMPLETE.

Another way of writing my formula would be:

=SUMPRODUCT((JobLogEntry!L2:L33331=B7)*(JobLogEnt ry!L2:L33331<=B9)*
(JobLogEntry!D2:D33331={"BY-PASS CAPSULE","apples","oranges","pears"})*
(LEFT(JobLogEntry!P2:P33331,8)="COMPLETE"))

See if this has any effect.

Hope this helps.

Pete

On Jan 22, 11:30*pm, wrote:
Biff and Pete,
* Thanks guys for helping me....I have tried both ways, but I can't
get either way to work....I can't see what the problem is here that
stops it from working. Biff's example:

=SUMPRODUCT(--(ISNUMBER(MATCH(JobLogEntry!D2:D33331,A22:A25,0))) ,--
(JobLogEntry!L2:L33331=B7),--(JobLogEntry!L2:*L33331<=B9),--(LEFT
(JobLogEntry!P2:P33331,8)=A25))

Pete I tried your formula it wouldn't work with more than piece of
equipment.
?????Puzzled!!
Ken

SUMPRODUCT with multiple criteria
 

Hi Pete,
I found that I had an extraneous character in the cell that wasn't
deleted, and it caused both formulas to not work. Both ways work very
well....Thank You both for your help and patience!
Ken

SUMPRODUCT with multiple criteria
 

Well, that's good to hear - thanks for feeding back.

Pete

On Jan 23, 12:14*am, wrote:
Hi Pete,
* I found that I had an extraneous character in the cell that wasn't
deleted, and it caused both formulas to not work. Both ways work very
well....Thank You both for your help and patience!
Ken