+ Reply to Thread
Results 1 to 4 of 4

Refer to 'custom' formatted date column in formula

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    76

    Refer to 'custom' formatted date column in formula

    Hi, I have a system generated report which has the date in CUSTOM formatted field. I am trying to create a summary report which counts instances which meet certain criteria including month. I have been using sumproduct and it works fine until I try and count the month information.

    I have attached a sample to explain what I am trying to do - the formula in Cell G10 counts everything that has GUI in column B and DOES NOT contain BCG in column C - The result is correct.

    The formula in cell G9 is the one I am trying to make work with the date criteria added - I have tried different variations of the date in the formula but it doesn't work. the data in column A is how it comes out of a different system and I don't want really have to mess about and change it I just want my summary sheet to update when I run this report and save it.

    Any ideas?
    Last edited by moneypennie21; 07-06-2017 at 05:20 AM. Reason: TYPO

  2. #2
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    76

    Re: Refer to 'custom' formatted date column in formula

    adding attachment
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,060

    Re: Refer to 'custom' formatted date column in formula

    You can use this formula to count only the entries for January:

    =SUMPRODUCT(($B$2:$B$7861="GUI")*($C$2:$C$7861<>"BCG")*(TEXT($A$2:$A$7861,"mmm")="Jan"))

    It's not a good idea to use full-column references with SUMPRODUCT, as the formula will search every cell in that range (i.e. 1 million + of them), and so will be very sluggish.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    76

    Re: Refer to 'custom' formatted date column in formula

    Brilliant, yes this works great, thank you. Yes I will amend the column references I just need to make it dynamic so will have to have a large range on it

+ 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. Formula to show percentage of cells in each segment in custom formatted cells
    By newbie_Lau in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-03-2016, 01:21 PM
  2. [SOLVED] Refer to last used column and fill formula
    By S1n1 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-26-2016, 02:47 PM
  3. [SOLVED] Replicating a custom formatted cell in a concatenated formula! I think!
    By Wilgoss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-14-2012, 03:34 PM
  4. Replies: 11
    Last Post: 02-18-2012, 10:12 PM
  5. Text from Date formatted column
    By kawhite in forum Excel General
    Replies: 3
    Last Post: 12-12-2007, 12:41 PM
  6. [SOLVED] Formula to refer to changing Column
    By Kenny Markhardt in forum Excel General
    Replies: 1
    Last Post: 10-10-2005, 07:05 PM
  7. How to refer to current column in a formula?
    By jmg092548 in forum Excel General
    Replies: 4
    Last Post: 08-10-2005, 03:05 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