Forum Discussion
Using IFBLANK with IF Formulas
Yes:
=IF(F1="", "", IF(F1="Standard", ..., IF(F1="Full", ...)))
or
=IFS(F1="", "", F1="Standard", ..., F1="Full", ...)
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!
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)))
If you wish, you might like my replies by clicking the thumbs up button, and if the forum allows you to mark more than one reply as the solution, do that. But it's not essential, your kind words are reward enough!
- Hans, BRILLIANT! That was it. I've duplicated the formula for each of my columns so the relative measurements were adjusted appropriately. Impossible for me to thank you enough. How may I give a positive review or endorsement? What would be helpful?
Select the range you want to format. The active cell in the selection should be in row 18.
On the home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND($C18="Standard",$G18>=DATE($Q$6,$R$6,$S$6),$G18<=DATE($Q$7,$R$7,$S$7))
Note that there is no $ before the row number 18. We want this to be relative.
Click Format...
Activate the fill tab.
Select a color.
Click OK, then click OK again.
Activate the Fill tab
- Not sure if Conditional Formatting is part of your expertise, Hans, but any idea why the following wouldn't work if I applied it to G18:G517?
=IFS($C$18:$C$517,"Standard",$G$18:$G$517,">="&DATE($Q$6,$R$6,$S$6),$G$18:$G$517,"<="&DATE($Q$7,$R$7,$S$7))
I'm trying to format (fill) the cells in the workbook that are within the category (standard or full) and within the date range so they're easier to identify. Thanks! - Brilliant! That's it, Hans. Thank you! I couldn't figure out how to tie C17:C18 to each range and make them a group. SO helpful. Thank you!
Try
=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))+COUNTIFS(C18:C517,"Standard",$J$18:$J$517,">="&DATE($Q$6,$R$6,$S$6),$J$18:$J$517,"<="&DATE($Q$7,$R$7,$S$7))+COUNTIFS(C18:C517,"Standard",$M$18:$M$517,">="&DATE($Q$6,$R$6,$S$6),$M$18:$M$517,"<="&DATE($Q$7,$R$7,$S$7)))
- Hans, this is helpful. Thank you. How would I build the formula if I need to check Column C for "Standard" or "Full" but then also check Columns G, J, and M for dates? If the columns are all the same shape and size, would it still work?