+ Reply to Thread
Results 1 to 9 of 9

SUMPRODUCT for information of MONTH and TEXT (2 criteria)

  1. #1
    Registered User
    Join Date
    10-05-2015
    Location
    Hong Kong
    MS-Off Ver
    2007
    Posts
    9

    SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    Hi all,

    I am currently working on a turnover report and i am having some problems using the countif formula to count e.g. the number of staff resigning on a certain month with a certain title.
    Capture.JPG

    I tried to search online and found that I will have to use SUMPRODUCT instead of COUNTIF for this matter as there are the month column and text column. However, I have not too familiar with this function.
    Can anyone help so that i can have the result of:
    in January, there are 1 Sales Associate leaver and 1 Senior Sales leaver and 1 Sales Associate leaver in Apr

    Note that I only look at the month instead of the date.
    thanks for your help in advance
    Last edited by mallory.chui; 10-13-2015 at 04:23 AM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    How about countifs.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    Here's a sample sheet. You could also do this by referencing cells. The date you see in cell E1 does not have to be January 1, it only has to be a date in January.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-05-2015
    Location
    Hong Kong
    MS-Off Ver
    2007
    Posts
    9

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    Thank you for your quick response. I have tried using your formula in the sample sheet and i think i understand how it works but when i try to use it in my report, no matter what date or position i type in, the result is 0.

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    Can you post your workbook?
    The data sample can be relatively small.

    If your report is sensitive make a copy delete all but a few rows and make up dummy info for the sensitive stuff. Leave the formulas as you tried them so I can see what is going on.

    ViewPic
    Last edited by skywriter; 10-05-2015 at 11:03 PM.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    Last edited by skywriter; 10-05-2015 at 11:04 PM.

  7. #7
    Registered User
    Join Date
    10-05-2015
    Location
    Hong Kong
    MS-Off Ver
    2007
    Posts
    9

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    it works fine now.
    I found out what's going wrong when i was trying to create the dummy file.
    I didnt realize the formula is date sensitive and simply used 31 as the last date of all months.
    Thank you for your help. i thought i couldnt use countif for this case. thanks heaps!!

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    I didnt realize the formula is date sensitive and simply used 31 as the last date of all months.
    My cell reference formula is not date sensitive and uses the end of month function to ensure it has the correct beginning and ending dates no matter what month or year it is, including leap years.

    With the hard coded date formula it is up to the user to enter the correct beginning and ending dates for the month and year in question.

    See post #6, by not acknowledging it, you may end up on some ignore lists, including mine.
    Last edited by skywriter; 10-05-2015 at 11:40 PM.

  9. #9
    Registered User
    Join Date
    10-05-2015
    Location
    Hong Kong
    MS-Off Ver
    2007
    Posts
    9

    Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

    thanks for the information. i have read the page in post #6. new to these forums didn't know much about how it actually works.

+ 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] Sumproduct with month, year, other criteria, and blank
    By bibbi2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2015, 12:47 AM
  2. [SOLVED] Sumproduct from date - 4 criteria (inc month & year)
    By Knawl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2014, 04:48 AM
  3. Using SUMPRODUCT For Two Criteria AND Month of Year.
    By stuu3270 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2013, 06:22 AM
  4. [SOLVED] Sumproduct/text/month
    By sick stigma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2012, 03:39 PM
  5. Sumproduct using Month as one of the criteria
    By Statsman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2008, 02:21 PM
  6. SumProduct error when using Year , Month and text criteria
    By jsrobin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2008, 10:56 PM
  7. sumproduct additional information for adding another criteria
    By Darlo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2007, 11:30 AM

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