+ Reply to Thread
Results 1 to 12 of 12

SUMPRODUCT count specific text criteria containing month year in a range, possible?

  1. #1
    Registered User
    Join Date
    09-01-2020
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Unhappy SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Hi guys,

    How are you all?!

    I am new here as a user although I have been following up many tutorials from this forum.

    Current trying to build a formula where I can count a specific text in Column "A" and allocate in a different cell base if it is open or closed by year and month.


    =SUMPRODUCT((MONTH($B$2:$B$1500)=E5)*(YEAR($B$2:$B$1500)=$D$5))*(A$2:A$1000="baseline")

    So for example I would like to count all cells with Open and baseline criteria happened in 01/01/2019 for example.

    ***workbook uploaded***


    Appreciate your help to crack this one, thanks in advance guys

    Danz-
    Attached Files Attached Files
    Last edited by Danz-; 09-01-2020 at 10:54 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    09-01-2020
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Hi AliGW thanks I will update the post
    Last edited by AliGW; 09-01-2020 at 10:30 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    I am asking for a sample workbook, not an update to your post.

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Ali is right but a very quick stab
    =SUMPRODUCT((MONTH($B$2:$B$1500)=E5)*(YEAR($B$2:$B$1500)=$D$5))*(A$2:A$1000="baseline")

    could be

    =SUMPRODUCT((MONTH($B$2:$B$1500)=E5)*(YEAR($B$2:$B$1500)=$D$5)*(A$2:A$1000="baseline"))

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Your sample workbook shows a formula that isn't working. What it needs to show, manually mocked up, is a few rows of results data as you want it to appear.

  7. #7
    Registered User
    Join Date
    09-01-2020
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Quote Originally Posted by AliGW View Post
    I am asking for a sample workbook, not an update to your post.
    Hi AliGW, I have updated a version 2 of the workbook. Hope this helps

    Thanks
    Last edited by Danz-; 09-01-2020 at 10:55 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Get rid of the formula and MANUALLY enter the results you want:

    Excel 2016 (Windows) 32 bit
    H
    1
    Open baseline
    2
    1
    3
    #REF!
    4
    0
    5
    0
    6
    1
    Sheet: Sheet1

  9. #9
    Registered User
    Join Date
    09-01-2020
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5
    Hi AliGW, the workbook attached is a merely example. The actual work have 2000 fields and I need that to be automated as a reflex of cells in green. thanks for your collaboration.
    Last edited by AliGW; 09-02-2020 at 05:56 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Registered User
    Join Date
    09-01-2020
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    5

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Well I guess no one knows who to crack this formula lol...

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    No one knows what you want as an answer. Hence the request to mock up required results. A formula can be expanded to your actual scenario, but in the first pace how can you write a formula when you do not knowthe result it is aiming for?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: SUMPRODUCT count specific text criteria containing month year in a range, possible?

    Quote Originally Posted by Danz- View Post
    Well I guess no one knows who to crack this formula lol...
    LOL!!! I think the joke's on you.

    As I said before, and as has been reiterated above, you need to give us what we are asking for first, THEN we'll provide you with a solution. We are waiting for you ...

+ 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 to count year values in range based on another cell year value
    By mr_mango81 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-04-2017, 12:46 AM
  2. [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
  3. [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
  4. Replies: 2
    Last Post: 12-17-2013, 06:30 AM
  5. 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
  6. Replies: 5
    Last Post: 02-04-2013, 05:32 PM
  7. 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

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