+ Reply to Thread
Results 1 to 7 of 7

Unable to sumproduct based off text in cell

  1. #1
    Registered User
    Join Date
    09-02-2018
    Location
    Camarillo, California
    MS-Off Ver
    2013
    Posts
    3

    Unable to sumproduct based off text in cell

    Hello, I am new to this forum. Hoping I can get some help and be of help to others. Soooo now to my problem. I have a list of 250 incidents with a date. I am trying to determine how many of the incidents occur each month based off the location. My location is a drop down with 5 different categories.
    Currently I am using =SUMPRODUCT((MONTH(Data!$A$2:$A$5000)=1)*(YEAR(Data!$A$2:$A$5000)>1999)) for the entire data.

    I tried =IF(Table25[[#All],[Location]]="Education",SUMPRODUCT((MONTH(Data!$A$2:$A$5000)=4)*(YEAR(Data!$A$2:$A$5000)>1999)),"nope") but that only looks for it in one cell.


    Thanks for the help, again I am new so if I need to try to explain it better please let me know,

    Scott

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Unable to sumproduct based off text in cell

    no sample file = no test
    =SUMPRODUCT(--(locationrange=location)*(MONTH(data!$A$2:$A$5000)=1)*(YEAR(data!$A$2:$A$5000)>1999))


    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to Quick Post button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Unable to sumproduct based off text in cell

    Try using countifS() instead
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    09-02-2018
    Location
    Camarillo, California
    MS-Off Ver
    2013
    Posts
    3

    Re: Unable to sumproduct based off text in cell

    Here is the attached file sorry for the delay. I had column as a table which made the file way to big so I created a new partial document
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Unable to sumproduct based off text in cell

    here you go, with an improved way of picking up the month from column A:

    =SUMPRODUCT(--(MONTH(Data!$A$2:$A$5000)=MONTH(1&D2))*(YEAR(Data!$A$2:$A$5000)>1999)*(Data!$H$2:$H$5000="Education"))

    cipied down.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    09-02-2018
    Location
    Camarillo, California
    MS-Off Ver
    2013
    Posts
    3

    Re: Unable to sumproduct based off text in cell

    Glenn that is awesome thank you!!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Unable to sumproduct based off text in cell

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Unable to show text from another cell in the message box
    By Krishnab4u in forum Excel General
    Replies: 2
    Last Post: 07-17-2018, 04:58 PM
  2. Replies: 2
    Last Post: 01-12-2015, 02:27 AM
  3. [SOLVED] Unable to copy text from a locked cell... Help!
    By danielneedssomehelp in forum Excel General
    Replies: 10
    Last Post: 08-08-2014, 12:01 AM
  4. Unable to send user-editable text to Outlook email body based on active cell.
    By m0meehan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2014, 01:51 PM
  5. [SOLVED] Unable to Put Cell Value into Internet Text Box
    By rlee12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2013, 08:18 PM
  6. Replies: 5
    Last Post: 02-04-2013, 05:32 PM
  7. unable to read cell text data
    By tehyavanna in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2010, 07:47 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