|
COUNTIF MULTIPLE CRITERIA
To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation.
Thanks will A |
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 |
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 |
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 |
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 |
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 |
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 |
COUNTIF MULTIPLE CRITERIA
This would work just as well, and do the same job:
=SUMPRODUCT((A1:A1000="aa")*(B1:B1000="bb")*(C1:c1 000="cc")) See Bob's web page on the subject to find out how and why. http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dan Shoemaker" <Dan wrote in message ... 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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
COUNTIF MULTIPLE CRITERIA
Try my LEFT version
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tim" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com