Tagged: countifs, formula syntax, formulas
- This topic has 7 replies, 2 voices, and was last updated 4 years, 5 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.