+ Reply to Thread
Results 1 to 8 of 8

What's wrong with this formula? (counting monthly data)

  1. #1
    Forum Contributor
    Join Date
    11-26-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    116

    What's wrong with this formula? (counting monthly data)

    Hi!

    I have a table which has the following columns:

    Date - Data1 - Data2 - OtherData1 - OtherData2


    I came up with some formulas to count my datas monthly. I have 12 tables with this kind of formula in it:

    Please Login or Register  to view this content.
    Where B12 is the year and A213 is my month number. My first try on the "date filter" looked like that:
    Please Login or Register  to view this content.
    And it wasn't working so I thought it was because the 31 wasn't a good idea for non-31-days-months but none of the formulas above are working.

    Any idea why ?

    (BTW, IDK why it's not working but I have data in my table for months 10, 11 and 12 and the only calculation tables that are calculating data are the ones for months 9 and 10. The results are the same in these two tables and are counting all my Table1[Data1] and [Data2] (the count is not monthly))


    Any help appreciated
    Last edited by dourpil; 01-31-2014 at 03:55 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: What's wrong with this formula?

    Before starting help, try to replace your title with giving more details which presents your issue, i.e "counting data monthly fails with days of month"
    Quang PT

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: What's wrong with this formula? (counting monthly data)

    Have you tried a Pivot Table ( with monthly grouping)?
    Are all the dates real dates or are some text looking like dates ? ( can be checked with the ISNUMBER function)

  4. #4
    Forum Contributor
    Join Date
    11-26-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: What's wrong with this formula? (counting monthly data)

    Quote Originally Posted by Pepe Le Mokko View Post
    Have you tried a Pivot Table ( with monthly grouping)?
    Are all the dates real dates or are some text looking like dates ? ( can be checked with the ISNUMBER function)
    I have a Pivot Table that I use for charting but I don't think it's very practical in this situation (I need to count Data1 and Data2. A pivottable can easily count Data1 and Data2 separately but won't sum the two results.. I'd rather have a formula I think.


    Oh and dates are dates
    Last edited by dourpil; 01-31-2014 at 05:53 AM.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: What's wrong with this formula? (counting monthly data)

    Could you post a small sample sheet?

  6. #6
    Forum Contributor
    Join Date
    11-26-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: What's wrong with this formula? (counting monthly data)

    Quote Originally Posted by Pepe Le Mokko View Post
    Could you post a small sample sheet?

    Here it is:

    monthlyissue.xlsx

    I found a formula that does the trick and replaces the countif function with a sumproduct, but it leaves me with other functions (average and a countif >0) that don't work because of how I write the month-filtering parts of my formulas
    Last edited by dourpil; 01-31-2014 at 08:39 AM.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: What's wrong with this formula? (counting monthly data)

    I see you are working with merged cells.. I really would avoid them if possible they are nothing but trouble and make analysis difficult.

    Any way yo work without hem?

    And, BTW Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
    Last edited by Pepe Le Mokko; 01-31-2014 at 11:32 AM.

  8. #8
    Forum Contributor
    Join Date
    11-26-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: What's wrong with this formula? (counting monthly data)

    I could remove the merged cells indeed, but there are none in the table where the actual data is. I have a few merged cells in my analysis worksheet which I only used for convenience. Are they the cause of the trouble I'm getting?

+ 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. [SOLVED] What is wrong with this formula?
    By AAAAmy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2012, 10:21 PM
  2. Wrong Formula
    By usmanfarok in forum Excel General
    Replies: 7
    Last Post: 06-25-2012, 04:20 AM
  3. What is wrong in my formula?
    By michele3000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2010, 05:29 AM
  4. Something wrong with my formula?
    By Lene in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-26-2009, 05:53 AM
  5. ###### Wrong Formula
    By Ningster in forum Excel General
    Replies: 8
    Last Post: 07-07-2006, 08:15 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