+ Reply to Thread
Results 1 to 10 of 10

Sum product as count if's with dates and gaps in data

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

    Sum product as count if's with dates and gaps in data

    Hi, I am using SUMPRODUCT as a COUNT IF across spreadsheets but I can't figure out how to do what I need.
    I want to look at the data and count the number of dates that fall in a specific week that meet criteria in another column as well This is the formula I am using

    Column V- this has the date information in but there are gaps
    C1 this cell has the WC date in
    Column N has the other criteria I am looking for in the data

    so basically I need it to count anything that has a match in column N but that has a date within a week of the WC date in CELL C1 (I have a variation of this date count in another report so I know this part works it is the blanks in the data that are the issue)

    =SUMPRODUCT(--('[training record LMS data.xlsx]Training record'!$V$4:$V$18545>=$C$1)*('[training record LMS data.xlsx]Training record'!$V$4:$V$18545>=$C$1+6)*('training record LMS data.xlsx]Training record'!$n$4:$n$18545="arrangement classroom")*('[training record LMS data.xlsx]Training record'!$V$4:$V$18545<>""))

    I have tried putting the last part of the formula the <>"" part in various different places and still can't get it to work.

    Any wizards out there that can fix this for me?

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,487

    Re: Sum product as count if's with dates and gaps in data

    Pl read the yellow banner on the top.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

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

    Re: Sum product as count if's with dates and gaps in data

    Hi kvsrinivasmurthy, i usually do attach examples but hadn't got round to creating a mock up one for this issue, I was hoping if I made the issue clear enough I could get a quick answer as I have in the past, however if I can't get an answer without an example now I will have to add one when I have time to do it as the only one I have has all my data in it

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,734

    Re: Sum product as count if's with dates and gaps in data

    =SUMPRODUCT(--('[training record LMS data.xlsx]Training record'!$V$4:$V$18545>=$C$1)*('[training record LMS data.xlsx]Training record'!$V$4:$V$18545>=$C$1+6)*('training record LMS data.xlsx]Training record'!$n$4:$n$18545="arrangement classroom")*('[training record LMS data.xlsx]Training record'!$V$4:$V$18545<>""))

    As written the red is irrelevant as the green is always going to take precedence the last term in blue seems irrelevant not being blank as it has to be greater than c1+6 earlier
    Last edited by davsth; 06-28-2021 at 11:15 AM.

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

    Re: Sum product as count if's with dates and gaps in data

    Ok I have tried removing the redundant parts you suggest but still can't get the formula to work - (the date between part was something I picked up from another question I posted somewhere and has always worked the way I want it to previously) however please see attached example data in Book 2 and table in book 3 - as far as I can tell the result should be what I have manually entered into column E unless I am missing something simple - entirely possible! I have tried variations on the formulas in columns B/C/D
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: Sum product as count if's with dates and gaps in data

    FILTERing on your data I get get the same results as the SUMPRODDUCT. I don't know how you get 10

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

    Re: Sum product as count if's with dates and gaps in data

    Hi JohnTopley, if you count everything for lesson B on 14th, 15th 17th June it comes to 10 - this is what I want everything counted for the week commencing 14th
    Last edited by moneypennie21; 06-28-2021 at 12:29 PM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,892

    Re: Sum product as count if's with dates and gaps in data

    Your test is for all dates >= 20th June ($B$1 +6) where $B$1=14/06/2021

    what you want is

    =SUMPRODUCT(--([Book2.xlsx]Sheet1!$B$2:$B$572>=$B$1)*([Book2.xlsx]Sheet1!$B$2:$B$572<=$B$1+6)*([Book2.xlsx]Sheet1!$A$2:$A$572=A2))

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,487

    Re: Sum product as count if's with dates and gaps in data

    Try this for B2
    Change this condition

    (Sheet1'!$B$2:$B$572>=$B$1+6)

    as

    (Sheet1'!$B$2:$B$572<=$B$1+6)*(Sheet1'!$B$2:$B$572>=$B$1)

    Also this condition

    (Sheet1'!$A$2:$A$572="Lesson A")

    as

    (Sheet1'!$A$2:$A$572=$A2)

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

    Re: Sum product as count if's with dates and gaps in data

    OMG Soooo simple just had > when I should have had <!! Thanks both this works fine now!

+ 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. Grouping dates with gaps in them.
    By kthomson1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-10-2019, 09:22 AM
  2. [SOLVED] Fix Gaps in Dates
    By amartin575 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-17-2018, 03:46 PM
  3. [SOLVED] Overlapping Dates, Gaps in Dates, Double Counting
    By arunkushvaha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 11:15 PM
  4. Replies: 5
    Last Post: 10-14-2012, 07:02 PM
  5. Overlapping Dates, Gaps in Dates, Double Counting
    By sglxl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2012, 02:46 AM
  6. listing gaps in dates
    By shakes347 in forum Excel General
    Replies: 4
    Last Post: 09-10-2010, 06:16 PM
  7. sum product to count data in a date list
    By thompsy121 in forum Excel General
    Replies: 12
    Last Post: 02-24-2010, 04:13 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