Need to populate counts from multiple workbooks into main report based on month

So my original post can be found here:
Original Post on this site

That request was solved and works great.. Now that i showed it to management, there is a small change that they want to see..

the current formula, will return the counts and if there is a zero, it will return blank BUT what they want is to have both blanks for any month that we are not currently in (May thru Jan) and in past months, show a zero if there are none..
Hope that makes sense.

Attached is screen shots of what we currently get from the working formula and what we want to see with the updates im looking for help with

So not sure how to go about this so that it works as they expect. Because the solution formula provided in the original post above, would return zeros in all months and that was just cluttered. so we only want to see zeros in the cells that have no records for that month that match the criteria and the month is over.



This is the current formula that is being used for the "Current" state screenshot

Please Login or Register  to view this content.

HAve you tried using cell format?

this custom format will only show positive and negative and blank for 0 values
Select the area and apply the custom format

Have you tried using cell format?

this custom format will only show positive and negative and blank for 0 values
Select the area and apply the custom format

You can still use the cell format, you do not have to remove the If, I never said anything about that.
The only this is that the Custom format will not show ANY zero values.
Another one is to apply a conditional format for 0 values 0 values only, if the month is in the future then give the font the same color as the fill color, then it 'looks' blank

I implemented the conditional formt to not show the 0 values if the date in the top row is in the future, but your sample is for the past so all values with 0 show up in the Dashboard

Originally Posted by Keebellah

I implemented the conditional formt to not show the 0 values if the date in the top row is in the future, but your sample is for the past so all values with 0 show up in the Dashboard

So it seems that my formula doesnt have to change, just add the conditional formatting? Is that correct?
Actually i looked and the formula no longer has the IF? is that correct?

Just want to make sure what changed so i can update the actual report with all the changes.

The formula without the if, and the conditional “blanks” the 0 values if the date above the month names is greater than today’s date