Forum Discussion

doit_2729's avatar
doit_2729
Copper Contributor
Jul 26, 2022

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 ...
Excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor

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.

 

Doit_1973's avatar
Doit_1973
Copper Contributor
Jul 26, 2022

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, 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 26, 2022

    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?

    • Doit_1973's avatar
      Doit_1973
      Copper Contributor
      Jul 27, 2022

      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, 

Resources