Count Value and blank cell using today' date criteria

[doit_2729]

Good day Tech Community,

The formula should count the cells based on their value, plus add empty cells to the total cell count if the date is today. 

I have tried my best but i am not pro so, here i am for help.

I would appreciated if someone can help me with this formula

Please see the attached file here for details.

Thank you, 

[#2]

@doit_2729 

=SUMPRODUCT(N(TEXT(A2,"TTT")=Report!$B$2:$B$27))+IF(A2=TODAY(),COUNTIF(Report!A2:A27,"="&""),0)

Does this return your expected result? The sum of range D14:D20 equals the number of cells in range B2:B27 in sheet Report. I had to translate the days in sheet Report because of my version of Excel.

[#3]

@OliverScheurich 

Hi Quadruple,

Much appreciated for the help. 

The formula is working as the end result is the same as i want, however, when date is change is not counting. 

As per today's date, it is counting only the empty cell ("D14") not adding out the cell value like count of "TUE" or "DI" and so for the other days. 

Can you please look into again?

Thank You, 

[#4]

@Doit_1973 

=SUMPRODUCT(N(TEXT(A2,"TTT")=Report!$B$2:$B$27))+IF(A2=TODAY(),COUNTIF(Report!$A$2:$A$27,"="&""),0)

Can you enter this formula in your sheet. The first suggestion had Report!A2:A27 instead of Report!$A$2:$A$27 i'm afraid. The dollar signs ($) were missing.

What version of Excel do you work with? The "TTT" part of the TEXT formula wasn't translated :  TEXT(A2,"TTT")   . T is for german "Tag", in english excel you'd have to enter D for "day":    TEXT(A2,"DDD")   . Does it work if you enter the equivalent for "TTT" or "DDD" according to you excel version?

[#5]

@doit_2729 

I think you are doing unnecessary complex calculations.

I tried to wrap it up.

My regional settings are de_DE so I'm using TTT instead of DDD. See also the instructions in the file.

[#6]

@OliverScheurich 

Hi Quadruple,

Great..!

Thanks for help and guiding me.

Its work now, Here is the screen shot of my test data.

Your prompt assistance is greatly appreciated. 

Just curious, how did you apply logic here? I mean a bit of explanation of how this formula works would be greatly appreciated. (Just want to learn the function).

Thank you, 

[#7]

Hi Detlef,
Hi Detlef,
A more simplistic version of the formula is presented in your post.
Getting expert help was a big relief for me.

I greatly appreciate your assistance.
May God Bless You.