Using IFBLANK with IF Formulas

[kenmcd1]

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 before it has a date value).

IFBLANK was working fine until I added the variable formula component: =IF(ISBLANK(G28)," ",('Tracking Sheet'!$B$13*'Tracking Sheet'!$B$10))

Variable value formula works if I'm not using IFBLANK: =IF($C26="Standard",('Tracking Sheet'!$B$13*'Tracking Sheet'!$B$10),('Tracking Sheet'!$F$13*'Tracking Sheet'!$F$10))

I can do one or the other - how do I do both. #StumpedInStLouis!

[#2]

@kenmcd1 

Does this do what you want? If not, in what way?

=IF(G28="","",'Tracking Sheet'!$B$13*'Tracking Sheet'!$B$10)

[#3]

@Hans Vogelaar Thanks, Hans! I'm no expert, but does that allow for a second conditional formula? What I need to do is create a conditional formula: If A1="Standard", perform this specific calculation but if A1="Full", perform the other specific calculation. Then, I need to wrap all that into an IFBLANK so that if F1 is blank, it does nothing but if F1 has a date value, it performs the above calculations based on the value of A1. Does that make sense?

[#4 · Accepted solution]

@kenmcd1 

Yes:

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

or

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

[#5]

Thanks, Hans! I'm stepping out but will try this next and respond here. Thank you!

[#6]

Hans, that was it! Took a couple passes to get the syntax correct but it worked. Thanks for your help! Turns out it created a COUNTIF problem when I added the new category ("Full") but I'm trying to work that out using the same logic. :face_with_rolling_eyes:  No luck so far. Can I post back here if I have trouble with COUNTIF or should I start a new thread? @Hans Vogelaar 

[#7]

@kenmcd1 

Feel free to post a reply in this discussion.

[#8]

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!

[#9]

@kenmcd1 

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

Thanks in advance.

[#10]

@Hans Vogelaar 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!

[#11]

@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)))

[#12]

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?

[#13]

@kenmcd1 

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)))

[#14]

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!

[#15]

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!

[#16]

@kenmcd1 

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

[#17]

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?

[#18]

@kenmcd1 

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!