1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Countif not working if a cell contains a formula?

    From Greg H@21:1/5 to All on Wed Feb 9 15:36:10 2022
    I have a row of cells D2:D30 that have formulas that calculate values derived from other cells. In cells D2:D30 (example, cell D3=V14+U15) . I see the numbers that are the result of the formulas displayed in all the cells. I want to count how many cells
    from D2:D30 have a value below an amount I enter into cell F2. =COUNTIF(D2:D30,"=<100*") returns only 0. However, there are several values in D2:D30 that are above 100. I believe COUNTIF is reporting 0 because the range is all formulas and not typed numbers. Is there a way to have excel see the values in the cells
    and return the correct count?

    Thank you!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Zaidy036@21:1/5 to Greg H on Wed Feb 9 20:21:20 2022
    On 2/9/2022 6:36 PM, Greg H wrote:
    I have a row of cells D2:D30 that have formulas that calculate values derived from other cells. In cells D2:D30 (example, cell D3=V14+U15) . I see the numbers that are the result of the formulas displayed in all the cells. I want to count how many
    cells from D2:D30 have a value below an amount I enter into cell F2.
    =COUNTIF(D2:D30,"=<100*") returns only 0. However, there are several values in D2:D30 that are above 100. I believe COUNTIF is reporting 0 because the range is all formulas and not typed numbers. Is there a way to have excel see the values in the cells
    and return the correct count?

    Thank you!

    Use "<=" and not "=<"

    You do not tell us the range of values in D2:D30, but 100* would be
    better written as the max value you actually want without the "*"

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)