Tagged: countifs, formula syntax, formulas

- This topic has 7 replies, 2 voices, and was last updated 4 years, 2 months ago by quasarn01.

- AuthorPosts
- September 26, 2018 at 17:29 #19996

AnonymousInactiveHi,

I try to use the formula COUNTIFS but I get an error. What is the correct use of this formula ?

I write this : COUNTIFS(B4:D4;”TRUE”;B2:D2;”FALSE”)

Best regards

September 26, 2018 at 18:58 #19997

quasarn01ParticipantJean, have you tried using COUNTIF for TRUE and another COUNTIF for FALSE instead of one COUNTIFS statement? Example: COUNTIF(B4:D4;”TRUE”) and then COUNTIF(B2:D2;”FALSE”)

If you need both results to be added together you could use =COUNTIF(B4:D4;”TRUE”)+COUNTIF(B2:D2;”FALSE”) instead of the COUNTIFS formula…

September 26, 2018 at 19:37 #19998

AnonymousInactiveHi,

Thanks for your quick answer.

I used COUNTIFS because I want to count the column that meet both criteria. I won’t know how much cells meet one criteria and how much meet the other.

September 28, 2018 at 13:10 #20012

AnonymousInactiveNo idea about how to use the formula COUNTIFS ?

September 28, 2018 at 15:42 #20013

quasarn01ParticipantJean, without knowing exactly the criteria you are trying to compare, it would be difficult to explain… The COUNTIFS function returns the count of cells that meet one or more criteria. If you are trying to simply count the true and false cells in certain columns, then the comparison columns need to match cell for cell… The columns can be next to each other or separated by other columns but they need to match exactly. Comparing a column with different counts of cells will either give you a “0” or an error… From the formula you have posted, you seem to be trying to compare cells that are in 3 columns to a set of cells on the same 3 columns, ie, B4:D4 and B2:D2. The first half of your formula is searching column B from cell 4 to the bottom of column B and then from the top of column D down to cell 4… The second half of your formula is searching column B from cell 2 to the bottom of column B and then from the top of column D down to cell 2… The problem that I see here is that the rows/columns you are trying to get criteria from are overlapping, thus, giving you and error… You may have to re-structure your table so as the columns/rows are separate and do not overlap…

September 28, 2018 at 15:46 #20014

quasarn01ParticipantYou can count the same set of colums/cells but you will have to use 2 COUNTIF formulas and add them together since the comparisons you are looking for fall within the same cell ranges – =COUNTIF(B4:D4;”TRUE”)+COUNTIF(B2:D2;”FALSE”)

September 29, 2018 at 11:29 #20015

AnonymousInactiveSo, what I want to do is compare two set of data and return number of column that reach all criterias. For my example :

B2:D2 => TRUE | FALSE | FALSE

B4:D4 => TRUE | TRUE | FALSEWith this formula, I want the return 1 : COUNTIFS(B4:D4;”TRUE”;B2:D2;”FALSE”)

The one you suggest, the result will be 4.If I understand what you explain, the formula works with range in column and not in row, so i must transpose my data. Right ?

September 29, 2018 at 15:26 #20037

quasarn01ParticipantThat should solve your problem of returning an error message…

- AuthorPosts

- You must be logged in to reply to this topic.