+ Reply to Thread
Results 1 to 8 of 8

Total number of the dates of a month that have data, so far

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Patras, Greece
    MS-Off Ver
    Excel 2003
    Posts
    11

    Total number of the dates of a month that have data, so far

    In Sheet1 (columns B and C), as shown at the attached workbook, I enter values, everyday.
    In Sheet2 I sum that values for every month. From the current month (October), I have a SUMIF function to get the total number of the dates that data cells have data (they aren't blanks) so far.
    What I want is to copy that function in all cells, for the next years, without change every month the range of COUNTIF.
    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Total number of the dates of a month that have data, so far

    Hi,

    Why not just use a Pivot Table.

    See attached
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-17-2010
    Location
    Patras, Greece
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Total number of the dates of a month that have data, so far

    Pivot Table is OK for the data AAA, BBB and the total work dates of each month. However, I need to get the total number of work dates till now, not all work days of the month, that I've already calculated.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Total number of the dates of a month that have data, so far

    Agios(Άγιε(ποιός Άγιος Καλησπέρα.

    Try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also check the format in column B-dates-.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Total number of the dates of a month that have data, so far

    Enter the formula On sheet2

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Total number of the dates of a month that have data, so far

    Quote Originally Posted by agios59 View Post
    Pivot Table is OK for the data AAA, BBB and the total work dates of each month. However, I need to get the total number of work dates till now, not all work days of the month, that I've already calculated.

    ..what makes you think a pivot table can't show you this? With a small change to your column G data this is easily done. See attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-17-2010
    Location
    Patras, Greece
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Total number of the dates of a month that have data, so far

    Many thanks to Fotis1991 (ευχαριστώ πολύ) and sktneer. Both of your ways work fine.
    Also, many thanks to Richard Buttrey. Pivot tables are a known mighty tool, but in this case the user, for whom I make that project, has a limited knowledge of Excel, so I try to make it as simple and automatic as I can.

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Total number of the dates of a month that have data, so far

    Ok. This is a matter of personal opinion... Generally speaking Pivot Tables is a missunderstanding issue. So:

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting number of unique dates per month in a list of duplicate dates
    By Rackle83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2013, 10:15 AM
  2. evaluate range of dates for total same month occurences
    By rthrelk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2012, 07:23 PM
  3. Replies: 4
    Last Post: 08-28-2012, 01:31 AM
  4. Replies: 16
    Last Post: 09-06-2010, 05:11 PM
  5. Replies: 6
    Last Post: 03-20-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1