+ Reply to Thread
Results 1 to 5 of 5

Calculate number of TEXT instances in a particular year

  1. 09-21-2021, 10:09 PM #1
    matrixpom
    matrixpom is offline
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Calculate number of TEXT instances in a particular year

    Hi all, hope everyone is safe & well.

    I have a chart that records sections of a building ITP document (inspection & Test Plan), this document has 17 section of different trades, these are submitted as a building progresses. What I am trying to do is track which trades didn't supply their particular section of the ITP in a particular year e.g. the text I want to count is "AF" (Applied For) or "REQ" (Required). I've put a simplified spreadsheet together to show in better detail. I need the formula to look through dates formatted as DD-MMM-YY (column B) & then calculate how many "AF's" there are in section "1", this is the formula I have thus far...& just not working?

    Please Login or Register  to view this content.
    Hope this makes some sense, chart attached ok, thanks again guys, greatly appreciated - Marc
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-21-2021, 10:28 PM #2
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Calculate number of TEXT instances in a particular year

    Maybe , at W13.

    =SUMPRODUCT(--($B$1:$B$701 > =DATE($V13,1,1)),--($B$1:$B$701 < =DATE($V13,12,31)),--(C$1:C$701="REQ"))

    Regards.

    Note : I'v found that if add ' before 2020 , your formula work! but you may need to add $ before B:B and V7 in cell X7.
    Note2: and should not refer 'all column', it's take a very long calculations.
    Last edited by menem; 09-21-2021 at 10:37 PM. Reason: add note
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  3. 09-21-2021, 10:34 PM #3
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: Calculate number of TEXT instances in a particular year

    W7=SUMPRODUCT((YEAR($B$2:$B$1000)=$V7)*($C$1:$S$1=W$6)*($C$2:$S$1000=$V$5))

    Copy across and down



    Type REQ or another code in V5
    Last edited by CARACALLA; 09-21-2021 at 10:40 PM.
    Reply With QuoteRegister To Reply
  4. 09-21-2021, 10:48 PM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Calculate number of TEXT instances in a particular year

    Hi,

    If your have the CountIfs() function available in your version of Excel then see the yellow cells in the attached for my answer.
    CountIfs for Matrixpom.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 09-22-2021, 12:20 AM #5
    matrixpom
    matrixpom is offline
    Forum Contributor
    Join Date
    04-22-2012
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    329

    Re: Calculate number of TEXT instances in a particular year

    Wow that was quick! All working great. Like the "Countifs" with the helper column, this will come in handy for a couple of other applications I have, awesome advise as always guys! Hope you all have a great day! - Marc
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate the number of instances of a certain time then evenly distribute the number
    By elleb in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-12-2017, 06:09 PM
  2. [SOLVED] Using SUMIF and COUNTIF to calculate number of particular instances against one team
    By ajw1982 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2013, 04:23 AM
  3. Excel 2007 : Formula To Calculate Number of Instances Across 12 Sheets (Broken Down By Department)
    By Christopher1990 in forum Excel General
    Replies: 16
    Last Post: 02-24-2012, 03:08 PM
  4. Calculate number of months in year
    By littlelax in forum Excel General
    Replies: 9
    Last Post: 02-02-2012, 09:01 AM
  5. to calculate Year to date number
    By mingali in forum Excel General
    Replies: 2
    Last Post: 08-07-2010, 10:36 AM
  6. Function to count number of text instances
    By davelarue in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-18-2008, 04:39 AM
  7. Find all text instances in a sheet and add one number from each row
    By Greg in forum Excel General
    Replies: 1
    Last Post: 01-31-2005, 08:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1