Count mulitple criteria with string text and two conditions in one column

[kbaker]

I need to count the number of items that meet one criteria in some columns and in other columns could be one critieria or another. Example:

I need to count those items that meet the following criteria: col d = Change Request, col A contains AVM, col b can be active or assigned, and col c can be minor or major. Should produce count of 2. I have tried many varieties of sumproduct, countifs, isnumbers, etc, but to no avail. Any suggestions would be appreciated.
[TABLE="width: 615"]
<TBODY>[TR]
[TD][TABLE="width: 681"]
<TBODY>[TR]
[TD]


[/TD]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD]AVMSDM</SPAN>
[/TD]
[TD]Active
[/TD]
[TD]minor</SPAN>
[/TD]
[TD]Change Request</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Closed</SPAN>
[/TD]
[TD]Major</SPAN>
[/TD]
[TD]Change Request</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD]AVMSDM</SPAN>
[/TD]
[TD]Active</SPAN>
[/TD]
[TD]minor</SPAN>
[/TD]
[TD]Problem Report</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD]A/ODSM
IMCC</SPAN>
[/TD]
[TD]Assigned</SPAN>
[/TD]
[TD]6 - Integration</SPAN>
[/TD]
[TD]Problem Report</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD]ASFAVM</SPAN>
[/TD]
[TD]Assigned</SPAN>
[/TD]
[TD]major</SPAN>
[/TD]
[TD]Change Request</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>
[/TD]
[TD][/TD]
[TD]IPSM</SPAN>
[/TD]
[TD]Pending</SPAN>
[/TD]
[TD]9 - Product Improvement</SPAN>
[/TD]
[TD]Change Request</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT(ISNUMBER(SEARCH("AVM",A1:A6))*(OR(B1:B6={"active","assigned"})*(OR(C1:C6={"major","minor"}))*(D1:D6="Change Request")))

 

[Special-K99]

Try (untested)

=SUMPRODUCT(--(D1:D6="Change request")*--(ISNUMBER(SEARCH("AVM",A1:A6)))*((B1:B6="Active")+(B1:B6="Assigned"))*((C1:C6="Minor")+(C1:C6="Major")))

 

[MrVillareal]

try:

=SUMPRODUCT((ISNUMBER(SEARCH("AVM",A1:A6)))*(B1:B6={"active","assigned"})*(C1:C6={"minor","major"})*(D1:D6="change request"))

 

[Rick Rothstein]

try:

=SUMPRODUCT((ISNUMBER(SEARCH("AVM",A1:A6)))*(B1:B6={"active","assigned"})*(C1:C6={"minor","major"})*(D1:D6="change request"))

Hmm, that formula looks familiar.

 

[kbaker]

Try (untested)

=SUMPRODUCT(--(D1:D6="Change request")*--(ISNUMBER(SEARCH("AVM",A1:A6)))*((B1:B6="Active")+(B1:B6="Assigned"))*((C1:C6="Minor")+(C1:C6="Major")))

This worked beautifully. thank you

 

[MrVillareal]

Hmm, that formula looks familiar.

Oops i didn't know that we have the same formula but you have an OR