I need a formula that will choose between two formulas. =COUNT(L2,M2,N2,O2,P2,Q2,R2) and =IF(S2<232,0,7).

In this instance, I'm weighing totals on given days individually vs. the total for the week. L2-R2 correspond to a numeric value above 43 on each of the days of the week. S2 represents the total numeric value for the week.

If any of L2-R2 have a numeric value above 43, 1 point is added in the first formula, contained in CELL V and that total is the point value for the week.

However if the total numeric value for the week exceeds 232 for the week, as indicated in the second formula, the point value for the week is 7.

I want a formula that decides that if =IF(S2<232,0,7) returns true, have that formula show its output. if it returns false, I want =COUNT(L2,M2,N2,O2,P2,Q2,R2) show its output.

As it stands right now, I have each formula in its own CELL and have to visually compare, which is tedious, given the sheer number of times I have to compare.


Formulas can contain other formulas: if(condition, formula1, formula2). In your example, one can do

=IF( IF(S2<232,0,7), IF(S2<232,0,7), COUNT(L2,M2,N2,O2,P2,Q2,R2) )

so that when the output of the first formula is True, the If gets used, and otherwise COUNT gets used.

The above can be simplified to

=IF(S2<232, 7, COUNT(L2,M2,N2,O2,P2,Q2,R2) )

but I included the more complicated version to show the general idea.

| improve this answer | |
  • oh i see it now. you use the formulas as return values. that works. just tested it out. thanks! – Nemesys Dec 26 '16 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.