+ Reply to Thread
Results 1 to 6 of 6

Date formula counting blank cells as January. How to it to not do that?

  1. #1
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Date formula counting blank cells as January. How to it to not do that?

    I have the following array formula to configure the net for that January. {=SUM(IF(MONTH(A10:A85)=1, E10:E85,0)} The problem is I don't attribute taxes to a given month. So when I have a tax line entered in the date column (A) is blank. Yet, it calculates that blank row for January.

    Example (I just put in random numbers as an example):

    example.png

    The January net is wrong. It should be 1,534.18, but it is subtracting the January taxes and February taxes even though there is no date line for those rows. It seems if there isn't a date, it automatically assumes it is 1 (and counts it for January).

    The February one works correctly. {=SUM(IF(MONTH(A10:A85)=2, E10:E85,0)} And all the others do as well.

    Is there a way to get that January formula to treat blank cells in the A column as blank rather than attributing them to month 1?
    Last edited by dsrt16; 06-08-2018 at 02:43 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Date formula counting blank cells as January. How to it to not do that?

    FYI: Blanks are seen as 0, which to Excel is the date 1/0/1900.

    Try this:

    =SUM(IF((MONTH(A10:A85)=1)*(A10:A85<>""),E10:E85) Ctrl Shift Enter

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Date formula counting blank cells as January. How to it to not do that?

    Perhaps a non array formula..

    =SUMIFS(E10:E85,A10:A85,">=1/1/2018",A10:A85,"<=1/31/2018")

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Date formula counting blank cells as January. How to it to not do that?

    FYI, Jonmo1's solution from post #3 is preferable if you only want to take the sum for one year.

    The solution given in post #2 will sum all February data, regardless of the year.

  5. #5
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Date formula counting blank cells as January. How to it to not do that?

    Thank you. Both of your solutions worked. I am new to array formulas so I wasn't sure where or how to add the (A10:A85<>"") part.

    Is the * symbol like an AND in an array? I learned an array couldn't do a nested IFAND, which is how I tried to add it the first time.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Date formula counting blank cells as January. How to it to not do that?

    Glad we could help. Thanks for the rep!

    Yes, the * symbol is used as AND just like the + symbol is used as OR.

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

+ 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. Replies: 6
    Last Post: 03-02-2016, 10:41 AM
  2. Replies: 7
    Last Post: 02-25-2016, 09:51 AM
  3. counting non blank cells in a date range
    By g4tso in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2014, 05:55 PM
  4. [SOLVED] How to avoid including blank cells in MONTH function for January months?
    By Salomey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2013, 06:12 PM
  5. Replies: 5
    Last Post: 01-31-2012, 08:48 PM
  6. Excel 2007 : Counting a Date Range + blank cells
    By Rino468 in forum Excel General
    Replies: 3
    Last Post: 11-01-2010, 02:40 PM
  7. Date formula counting blank cells
    By kerndogg in forum Excel General
    Replies: 2
    Last Post: 12-09-2009, 02:13 PM

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