If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Two criteria for CountIf?
I have two columns of cells that need to be evaluated and determine if the
criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#2
|
|||
|
|||
Two criteria for CountIf?
=SUMPRODUCT(--(A1:A3"closed"),--(NOT(ISBLANK(B1:B3))))
replace A1:A3 and B1:B3 with the appropriate ranges DB_Bill wrote: I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#3
|
|||
|
|||
Two criteria for CountIf?
Try:
=SUMPRODUCT(--(A1:A100"Closed"),--(B1:B100""),--(B1:B100" ")) Sumproduct must have a range (rather than a column) HTH "DB_Bill" wrote: I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#4
|
|||
|
|||
Two criteria for CountIf?
=SUMPRODUCT(--(A1:A100"Closed"),--(TRIM(B1:B100)""))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DB_Bill" wrote in message ... I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#5
|
|||
|
|||
Two criteria for CountIf?
Thank you for the reply.
I am not trying to achieve the product of a sum, rather get a count of rows that have something other than the string "closed" and a value other than blank in the second column. The Countif function seems to be similar to what I need, however, it is only allowing me to specify criteria for one column at a time. This is not allowing both criteria to be checked and validated. An example would be like: columnA columnB closed 2100 closed unknown 500 The last row would be counted and give a return of 1. "Toppers" wrote: Try: =SUMPRODUCT(--(A1:A100"Closed"),--(B1:B100""),--(B1:B100" ")) Sumproduct must have a range (rather than a column) HTH "DB_Bill" wrote: I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#6
|
|||
|
|||
Two criteria for CountIf?
Bill, the formula that Toppers gave you will return 1, its work.
as sumproduct return 1 and 1*1=1 it will sum how many 1's do you have in your range, working like a countif. hope it helps Regard from Brazil Marcelo "DB_Bill" escreveu: Thank you for the reply. I am not trying to achieve the product of a sum, rather get a count of rows that have something other than the string "closed" and a value other than blank in the second column. The Countif function seems to be similar to what I need, however, it is only allowing me to specify criteria for one column at a time. This is not allowing both criteria to be checked and validated. An example would be like: columnA columnB closed 2100 closed unknown 500 The last row would be counted and give a return of 1. "Toppers" wrote: Try: =SUMPRODUCT(--(A1:A100"Closed"),--(B1:B100""),--(B1:B100" ")) Sumproduct must have a range (rather than a column) HTH "DB_Bill" wrote: I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
#7
|
|||
|
|||
Two criteria for CountIf?
Thank you for the help. After looking at the replies more closely I realized
what was happening in the solutions given. "Bob Phillips" wrote: =SUMPRODUCT(--(A1:A100"Closed"),--(TRIM(B1:B100)"")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DB_Bill" wrote in message ... I have two columns of cells that need to be evaluated and determine if the criteria are met in order to be included in a count. The first column needs to have something other than "Closed" and the second column and row should contain something other than null/blank. Is there a simple way to include both criteria in a single statement to allow a count of the rows? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access Custom Dialog Boxes for Report Criteria | FrankP | General Discussion | 1 | January 21st, 2006 11:36 AM |
Criteria problems in the DSUM function | AA | Worksheet Functions | 5 | January 10th, 2006 10:16 PM |
criteria, checkboxes, and/or | bicyclops | Running & Setting Up Queries | 4 | September 13th, 2005 10:47 PM |
sorting more than 3 keys | Brooke | General Discussion | 3 | June 18th, 2005 04:52 AM |
Merging MS Word document with MS Access 2002 criteria query | Doug Robbins | Mailmerge | 2 | November 4th, 2004 05:57 PM |