Error in formula with OR

Question

Some reason this doesn’t work?

I intend that he counts how many situations exist within one or another criterion.

Both this formula:

=CONTAR.SE.S(E:E;">120";E:E;"<139" OU F:F;">80";F:F;"<89")

Like this:

=CONTAR.SE.S((E:E;">120";E:E;"<139")OU(F:F;">80";F:F;"<89"))

Doesn’t work.

Translating better what I want is this: I want the total value of a table in which the values in column E are between 120 and 139 or in column F the values are between 80 and 89. If this line has both situations as true it will only count one.

Type an if, if condition 1=true then account 1 passes to the next case otherwise checks condition 2 and if this is true account 1 and passes to the next

Answer 1

Heed: I don’t know if this is due to the Excel version, but in my Excel in Portuguese (version 14.0.7149.5000 32 bits of Office 2010) nay exists a function called CONTAR.SE.S. The function that exists in my Excel is CONT.SES (and so I use it in the example). Still, Assuming your role is with the correct name, the answer is still valid (simply change the function name).

The function CONT.SES, Counting with multiple criteria, allows you to count using multiple criteria and multiple intervals. So, to have the calculation that you want to just do three counts:

1. Count the values in column E that are within the interest ranges of that column
2. Count the values in column F that are within the interest ranges of that column
3. Count the values in both columns within their respective ranges.

If you add up the values of (1) and (2), it will almost always be right, except when the exceptions you mention occur (the values of both columns E and F on a given row are both accepted). These exceptions are accounted for by (3), so just subtract this value from the previous sum.

For example:

=CONT.SES(E:E;">120";E:E;"<139")+CONT.SES(F:F;">80";F:F;"<89")-CONT.SES(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89")

To avoid the values being fixed in the formula, you may want to configure other cells with these values (for example, cells I1, J1, K1 and L1). In that case, make the formula like this:

=CONT.SES(E:E;">"&I1;E:E;"<"&J1)+CONT.SES(F:F;">"&K1;F:F;"<"&L1)-CONT.SES(E:E;">"&I1;E:E;"<"&J1;F:F;">"&K1;F:F;"<"&L1)

Here is an example of a spreadsheet (which you could have provided - would greatly facilitate the life of anyone who would help you), with the result (the cells "In E", "In F" and "In E and F" contain the partial formulas to demonstrate the results on each previously exposed item). The line indicated by the arrow in red is the only one where the conditions are met in both columns.

Answer 2

Several answers have already been submitted, but here is a more concise alternative to what you want to get.

=SOMA(SE((E:E>120)*(E:E<139)+(F:F>80)*(F:F<89);1;0))

A detail quite a lot important: When you finish inserting this formula, you need to press CTRL+SHIFT+ENTER. If you don’t, the formula won’t work.

Answer 3

The syntax of the function OU is wrong. It works like this:

OU(condição1, condição2,...,condiçãoN)

If any of the conditions within the function are true, it returns true.

Behold this article on the use of functions OU and SE together, and read the function documentation OU here