COUNT Formula recognising blank cells with formula

[Fayecattini]

I am trying to use the count formula to show filled cells and ignore blank cells, however i can't seem to get the formula to not count the blank cells that contain a formula.
I have tried to use COUNTIF but can't seem to get the count to show anything but the full amount of cells in the field.

I have attached the sheet i am working on. The data page is fed by the report page and i am trying to get the green column on the report page to count only cells that have a coloured red block in them and ignore the blanks.

[Richard Buttrey]

I note the file is extremely large - and has been zipped, which is a nuisance factor for some

All we need is a representative example. Hence you'll probably get more responses if you delete most of the data and upload a much smaller file excel file, not a zipped file

[Fayecattini]

Thank you - I have now uploaded as a smaller file unzipped

[Fayecattini]

Also i need to amend the formula for the formatting slightly:

On the report tab the red blocks are showing the data that is input in the 'project number' column on the data entry page. If i change the number to text the formatting disappears and the count does not work.
What i would like to do is amend the formula so that i can input text into the 'project number' box and all of the formatting remains the same. I managed to do it with numbers but not with text!

I have highlighted a row on the report tab to show what i mean

[Glenn Kennedy]

In D3, copied across and down:

=IFERROR(IF(D$3="","",INDEX(Table1[[Project Number]:[Project Number]],MATCH(1,($B4=Table1[[Resource Name]:[Resource Name]])*(D$3>=Table1[[Start Date]:[Start Date]])*(D$3<=Table1[[End Date]:[End Date]]),0))),"")

I modified one of the CF terms, too, but isn't quite right yet!!

[Glenn Kennedy]

Fixed the CF, too.

[Fayecattini]

Hi,

Thank you! That is great, did this take into account my first message as below? I know i put a few messages on here!

I am trying to use the count formula to show filled cells and ignore blank cells, however i can't seem to get the formula to not count the blank cells that contain a formula.
I have tried to use COUNTIF but can't seem to get the count to show anything but the full amount of cells in the field.

I am trying to get the green column on the report page to count only cells that have a coloured red block in them and ignore the blanks.

[Glenn Kennedy]

Nope. I didn't look at the early posts... and I'm notoriously poor at reading lots of words!! I prefer to see things!!

=SUMPRODUCT(--(LEN(D4:AE4)>0))

copied down.

[Fayecattini]

Once again thank you, one last thing...

I have added in rest date columns on the data sheet as some of the date ranges will require days off. Is there a way i can add them into my formula so that the dates shown in that column overwrite the red blocks and just show as white spaces?

[Glenn Kennedy]

I assume the rest days are simply Saturdays and Sundays... or are they something else?

[Fayecattini]

No they could be any day of the week...

the example on the sheet is the project dates being 01/02/2021-12/02/2021 with the rest dates being 07/02/2021 & 08/02/2021 (Sunday and Monday)

[Glenn Kennedy]

Try this out... my brain hurts...

[Fayecattini]

YES!! Thank you so much, that was exactly what i needed

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[Fayecattini]

Good Morning,

Could you advise how i change the rest day colour from white to amber or grey?

Also i have added in a year view tab and have linked the data from the confirmed month view tab so you can have a yearly screenshot. However when i change the month selected in the month view all of the months across the year view change to match the month i have selected? Is there something i am missing in the formula?

Thanks

[Fayecattini]

Good Afternoon,

Is there a way that i can get the formula to recognise the rest day as data and fill this will a coloured 'hashed' cell rather than being blank as it is currently?

Will i need to take the rest day out of the current formula for this to work?

Thanks

[Glenn Kennedy]

Remind me. Rest days? what and where are they shown

[Fayecattini]

Rest days are input on the data sheet. On each month tab the formula is set that if a date is put into the rest days column on the data sheet, then the cell on the corresponding month view will be blank.

[JeteMc]

Try changing the array entered formula** in cell D5 of the March sheet to read:

Please Login or Register  to view this content.

**Reminder that array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT before copying the formula over and down.
Note that the existing conditional formatting rules fill the rest cells.
The formula in column C should also be changed to read:

Please Login or Register  to view this content.

Let us know if you have any questions.