Home |
Search |
Today's Posts |
|
#1
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 |
#3
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 |
#4
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 |
#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")) |
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 |