SUMPRODUCT with multiple criteria

  • Thread starter Thread starter appamerican
  • Start date Start date
A

appamerican

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:P33331="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
 
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
 
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
 
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"))
 
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
 
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 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
 
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)*(JobLogEntry!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
 
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
 
Back
Top