Forum Discussion

kenmcd1's avatar
kenmcd1
Copper Contributor
Sep 25, 2021
Solved

Using IFBLANK with IF Formulas

Using Excel for Mac 16.52 and have an (IF this value, this formula, default formula) set up but am trying to use IFBLANK up front so it only generates a result when it's supposed to (when the cell be...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Sep 25, 2021

    kenmcd1 

    Yes:

     

    =IF(F1="", "", IF(F1="Standard", ..., IF(F1="Full", ...)))

     

    or

     

    =IFS(F1="", "", F1="Standard", ..., F1="Full", ...)

kenmcd1's avatar
kenmcd1
Copper Contributor
Oct 02, 2021
Hans, I'm stumped. I have a cell with an ISBLANK function. The True result is "", the false result is a COUNTIF formula. Here's the formula I'm trying to build - I need to count the cells where:
- The cell in the C column = "Standard" (I have a drop down, 2 options)
- The Dates in columns G-O are within a date range specified in Q, R, & S.
I can do one or the other, but when I try to put them together, it breaks and I get #VALUE! Here's what I'm using now:

=IF(AND(ISBLANK($G$18:$G$517),ISBLANK($J$18:$J$517),ISBLANK($M$18:$M$517))," ",COUNTIFS(C18:C517,"=Standard",$G$18:$O$517,">="&DATE($Q$6,$R$6,$S$6),$G$18:$O$517,"<="&DATE($Q$7,$R$7,$S$7)))

If I take out the date portion, it counts Cells with "Standard" accurately. If I take out the Standard portion, it counts the dates in the cell range accurately. I just can't seem to do both and I'm guessing it's a syntax issue. Any thoughts? Thanks in advance!
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 03, 2021

    kenmcd1 

    Could you attach a small sample workbook without sensitive data? That would allow me to experiment.

    Thanks in advance.

    • kenmcd1's avatar
      kenmcd1
      Copper Contributor
      Oct 03, 2021

      HansVogelaar Thanks, Hans. Let me know if this opens for you. The cells I'm trying to fix are S9, S10, S11, and V9, V10, V11. Thank you again for your help!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 03, 2021

        kenmcd1 

        Thanks.

        The problem is thast all ranges in COUNTIFS must have the same shape and size.

        In S9:

        =IF(AND(ISBLANK(G18:G517),ISBLANK(J18:J517),ISBLANK(M18:M517)),"",COUNTIFS(C18:C517,"Standard",$G$18:$G$517,">="&DATE($Q$6,$R$6,$S$6),$G$18:$G$517,"<="&DATE($Q$7,$R$7,$S$7)))

        And in V9 probably

        =IF(AND(ISBLANK(G18:G517),ISBLANK(J18:J517),ISBLANK(M18:M517)),"",COUNTIFS(C18:C517,"Full",$G$18:$G$517,">="&DATE($Q$6,$R$6,$S$6),$G$18:$G$517,"<="&DATE($Q$7,$R$7,$S$7)))

Resources