Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct - Multiple Criteria | Excel Discussion (Misc queries) | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
HELP...SUMPRODUCT with multiple criteria | Excel Discussion (Misc queries) | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions |