• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIF syntax

D

Danyong2

New Member
Hi, I would like to insert a formula that checks 2 columns of data for a certain Criteria if it is found in either of the column, to add to the count. How do I do that? The following formula does not work. Hope you could help. Thanks

=COUNTIF(TABA!A$1:A$80,TABB!B$1:B$80,"=New Member")
 
Danyong

Firstly, Welcome to the Chandoo.org Forums

try: =COUNTIF(TABA!A$1:A$80,TABB!B$1:B$80,"New Member")
 
Thanks for the reply. Tried that but got the message "You've entered too many arguments for this function
 
If you have two tables I would expect to see a separate count for each
= COUNTIF( Table1[Field1], Criterion ) + COUNTIF( Table2[Field2], Criterion )

If the two tables are a logical part of the same dataset (they must have an identical number of rows in each) you could have a combined condition but that would require a particular record to satisfy BOTH conditions simultaneously.
= COUNTIFS( Table1[Field1], Criterion1, Table2[Field2], Criterion2 )
I very much doubt that this is what you want.
 
Hi Hui,

Thanks for the reply. Tried but still got error. Sample file as attached. Thanks
 

Attachments

  • Sample_Excel.xlsx
    13.7 KB · Views: 3
Danyong2
TABA Cell C12 ... have You reread that sentence few times?
TABB Cell C12 ... is more clear.
 

Attachments

  • Sample_Excel.xlsx
    13.2 KB · Views: 6
I too failed to understand the first requirement.
Because COUNTIFS only implements AND conditions, I negated the conditions and subtracted the 'failures' from the total number of records.
= ROWS(TableB) - COUNTIFS(
TableB[Status], "<>New Member",
TableB[New Status], "<>New Member" )
 

Attachments

  • Sample_Excel (PB).xlsx
    16.8 KB · Views: 7
Hi Vletm and Peter,
Thanks for the response. The first table is just a variation of TableB, using Columns across 2 Tabs instead of the same table. Peter's sample is what I am looking for however, I am not sure how to use the [Status] and [New Status] would appreciate your explanation on what is required. Thanks!
 
Or….......................

1] In "TABA" sheet G2, enter :

=SUMPRODUCT(0+((TableA[Status]="New Member")+(TableB[New Status]="New Member")>0))

2] In "TABB" sheet G2, enter :

=SUMPRODUCT(0+((TableB[Status]="New Member")+(TableB[New Status]="New Member")>0))

Regards
Bosco
 
"I am not sure how to use the [Status] and [New Status] would appreciate your explanation on what is required. Thanks!"

The [Status] and [New Status] are structured references which come about because I converted the ranges on TABA and TABB to Excel Tables. Provided the Table is appropriately named and column headings are well chosen, the notation can be very informative, though perhaps a little verbose. Unlike conventional range names or direct referencing, the structured references automatically adjust to accommodate new data as it is added.

https://support.office.com/en-us/ar...l-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E
 
You must log in or register to reply here.
Back
Top