COUNTIF MULTIPLE CRITERIA

W

will A

To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
Thanks
will A
 
R

Ron Coderre

Try this:
=SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc"))

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A1000="aa"),--(B1:B1000="bb"),--(C1:c1000="cc"))

SUMPRODUCT does not work on a complete column, just a defined range, and all
ranges must be the same size

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
W

will A

Will define the range and try it. Thanks Bob.

Bob Phillips said:
=SUMPRODUCT(--(A1:A1000="aa"),--(B1:B1000="bb"),--(C1:c1000="cc"))

SUMPRODUCT does not work on a complete column, just a defined range, and all
ranges must be the same size

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
W

will A

Thanks will try it Ron.

Ron Coderre said:
Try this:
=SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc"))

Adjust range references to suit your situation

Does that help?

***********
Regards,
Ron
Click to expand...
 
D

Dan Shoemaker

This solved my problem as well but how does this work? what does the -- do?
 
T

Tim

I am using the formula =SUMPRODUCT(--(B2:B12="Active*"),--(E2:E12="*")) to
obtain a total count but receive 0.
I want to count where entries in range B = "Active" and entries in range E
is not null. HELP?!?!

B C D E
2 Active AC745867 AC745867 AC745867
3 Active AD041297 AD041297
4 Active AD041298 AD041298 AD041298
5 Active AD041299 AD041299 AD041299
6 Active AD041300 AD041300 AD041300
7 Active AD041313 AD041313
8 Active AD041312 AD041312 AD041312
9 Active AC290419 AC290419 AC290419
10 Active AC747441 AC747441 AC747441
11 Active AD041302 AD041302 AD041302
12 Active AD041311 AD041311
 
B

Bob Phillips

=SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Ron Coderre

Thanks for posting a sample of your data and the formula you are working
with...that helps you get better solutions.

Try this:
=SUMPRODUCT(--(B2:B12="Active"),--(E2:E12=""))

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Ron Coderre

Ah, yes.....
It's a good thing SOMEBODY was paying attention!
Thanks for noticing that, Bob.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
T

Tim

That worked! Thank you Bob......
Now to throw in one more detail. How can I check with a wildcard for
"Active", as I have "Active", "Active-T" and "Active-E" in column B.
If I try the wildcard "Active*", it returns zero (0) again....
 
R

Ron Coderre

Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH("Active",B2:B12)),--(E2:E12<>""))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
B

Bob Phillips

Number of ways.

If it starts with Active

=SUMPRODUCT(--(LEFT(B2:B12,6)="Active"),--(E2:E12<>""))

If it contain Active

=SUMPRODUCT(--(ISNUMBER(FIND("Active"B2:B12))),--(E2:E12<>""))

If it contains Active, active, aCTive, etc.

=SUMPRODUCT(--(ISNUMBER(SEARCH("Active"B2:B12))),--(E2:E12<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
T

Tim

I was heading down that path.......I think this does it.
I have to validate the results though as it looks off, but think I can take
it from here.

THANK YOU!!
 
T

Tim

Figured out I do have one problem with this calc....

The entries in column B are a combination of Active & Inactive - i.e.
"Active", "Active-E", "Active-T", "Inactive-E", "Inactive-T"

So using the Search function for "Active" counts both Active & Inactive.

Anyway around this??
 
T

Tim

GOAL!!!!!!!

Thank you both VERY MUCH!


Bob Phillips said:
Number of ways.

If it starts with Active

=SUMPRODUCT(--(LEFT(B2:B12,6)="Active"),--(E2:E12<>""))

If it contain Active

=SUMPRODUCT(--(ISNUMBER(FIND("Active"B2:B12))),--(E2:E12<>""))

If it contains Active, active, aCTive, etc.

=SUMPRODUCT(--(ISNUMBER(SEARCH("Active"B2:B12))),--(E2:E12<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
B

Bob Phillips

Try my LEFT version

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top