Forum Discussion

Stokely77's avatar
Stokely77
Copper Contributor
May 21, 2019

Multiple IF'S

Hi,

 

I've been trying to do this for ages!

 

I've got three cells on worksheet 1 and if any of them drop below 60, I want another  single cell on worksheet 3 to increase by 1. I've been trying this formula:-

=IF(Sheet1!D2<60,1,0)&IF(Sheet1!D3<60,1,0)

but instead of changing to 2 if 2 cells drop below 60, it displays 11.

 

Any tips where I'm going wrong?

 

Thanks in advance of any suggestions!

Formulas and Functions

5 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 21, 2019
    If the cells from which you want to evaluate whether any of them is below 60 are in Sheet1!D2:D4 and the cell you want to increase by 1 is in Sheet3!A2, the formula in Sheet3!B2 is:
    =(COUNTIF(Sheet1!$D$2:$D$4,"<"&60)>0)+A2
    Instead of hard-coding 60 in the foregoing formula, I suggest you store in a cell, such as Sheet3!B1, so that you can modify its value without modifying this formula:
    =(COUNTIF(Sheet1!$D$2:$D$4,"<"&B1)>0)+A2
    Note that the foregoing formula returns the value in Sheet3!A2 increased by 1, if any of the cells in Sheet1!$D$2:$D$4 is below 60; otherwise, it returns the value in Sheet3!A2 as is.
      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 22, 2019
        It seems your appreciation for me is superior as indicated by your use of the adverb “very”! Nonetheless, I want to know if my suggested formula returns your desired result. It behooves me to be apprised thereof.
  • Eva Vogel's avatar
    Eva Vogel
    Iron Contributor
    May 21, 2019
    Hello! Replace the „&“ in your formula with a „+“. The & is a sign for excel to take your if results as a text concatenation. So it makes 11, not 2. All the best, Eva

Resources