| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF MULTIPLE CRITERIA
To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
Thanks will A |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Try this:
=SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Thanks will try it Ron.
"Ron Coderre" wrote: Try this: =SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Im getting 0 (zero). what do you mean adjust range reference? thanks
"Ron Coderre" wrote: Try this: =SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
=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) "will A" wrote in message ... To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Will define the range and try it. Thanks Bob.
"Bob Phillips" wrote: =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) "will A" wrote in message ... To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
This solved my problem as well but how does this work? what does the -- do?
"Bob Phillips" wrote: =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) "will A" wrote in message ... To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
=SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<""))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Ah, yes.....
range E is NOT null<< It's a good thing SOMEBODY was paying attention! Thanks for noticing that, Bob. *********** Regards, Ron XL2002, WinXP-Pro "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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.... "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
Try this:
=SUMPRODUCT(--ISNUMBER(SEARCH("Active",B2:B12)),--(E2:E12<"")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Tim" wrote: 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.... "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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!! "Ron Coderre" wrote: Try this: =SUMPRODUCT(--ISNUMBER(SEARCH("Active",B2:B12)),--(E2:E12<"")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Tim" wrote: 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.... "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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?? "Ron Coderre" wrote: Try this: =SUMPRODUCT(--ISNUMBER(SEARCH("Active",B2:B12)),--(E2:E12<"")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Tim" wrote: 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.... "Bob Phillips" wrote: =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
GOAL!!!!!!!
Thank you both VERY MUCH! "Bob Phillips" wrote: 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) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(B2:B12="Active"),--(E2:E12<"")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF MULTIPLE CRITERIA
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 "Tim" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Countif with multiple criteria in the same column. | Excel Discussion (Misc queries) | |||
| COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
| countif function with multiple criteria | Excel Discussion (Misc queries) | |||
| Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
| COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Hybrid Mode
