IF.ERR.ENS and OR condition
Solved
touroul
Posted messages
515
Registration date
Status
Membre
Last intervention
-
touroul Posted messages 515 Registration date Status Membre Last intervention -
touroul Posted messages 515 Registration date Status Membre Last intervention -
Good evening forum
Do you know if it's possible to combine the COUNTIFS function and the OR function?
Here is a preview of my current formula:
=COUNTIFS(B4:B10,A4,D4:D10,{"OPEL","BMW"})
or: =COUNTIFS(B4:B10,A4,D4:D10,OR("OPEL","BMW")
The goal is of course to count the values that meet the criteria B4:B50;A4, and both OPEL or BMW.
These formulas do not work correctly.
Given the length of my initial formula, I would like to avoid duplicating the formula like this:
=COUNTIFS(B4:B10,A4,D4:D10,"OPEL",D4:D10,"BMW")
Thank you in advance for your help.
Configuration: Windows / Excel 2013 / Firefox 51.0
Do you know if it's possible to combine the COUNTIFS function and the OR function?
Here is a preview of my current formula:
=COUNTIFS(B4:B10,A4,D4:D10,{"OPEL","BMW"})
or: =COUNTIFS(B4:B10,A4,D4:D10,OR("OPEL","BMW")
The goal is of course to count the values that meet the criteria B4:B50;A4, and both OPEL or BMW.
These formulas do not work correctly.
Given the length of my initial formula, I would like to avoid duplicating the formula like this:
=COUNTIFS(B4:B10,A4,D4:D10,"OPEL",D4:D10,"BMW")
Thank you in advance for your help.
Configuration: Windows / Excel 2013 / Firefox 51.0
2 réponses
Hello Vaucluse
Like you, I reached the same conclusion while browsing the forums.
There is no possibility with COUNTIFS
However, I came across a post where someone managed to do it with a formula like:
=COUNTIFS(B4:B10,A4,D4:D10,{"OPEL","BMW"}), without array validation.
For me, this syntax does not work, with no formula error, but it returns nothing.
So, I tried your suggestion with:
=SUMPRODUCT((B4:B10=A4)*((COUNTIF($B$1:$B$5,D4:D10)))
It works well, and the results are correct.
Given the length of the initial formula, I'm still saving about 1/3 of the lines, but more importantly, I gain clarity in the formula.
Thank you very much, and I'll mark it as resolved; it was interesting!
See you soon!
Like you, I reached the same conclusion while browsing the forums.
There is no possibility with COUNTIFS
However, I came across a post where someone managed to do it with a formula like:
=COUNTIFS(B4:B10,A4,D4:D10,{"OPEL","BMW"}), without array validation.
For me, this syntax does not work, with no formula error, but it returns nothing.
So, I tried your suggestion with:
=SUMPRODUCT((B4:B10=A4)*((COUNTIF($B$1:$B$5,D4:D10)))
It works well, and the results are correct.
Given the length of the initial formula, I'm still saving about 1/3 of the lines, but more importantly, I gain clarity in the formula.
Thank you very much, and I'll mark it as resolved; it was interesting!
See you soon!
