Forum Discussion
Count Value and blank cell using today' date criteria
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,
6 Replies
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.
- 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.
=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.
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,
=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?
