+ Reply to Thread
Results 1 to 8 of 8

Adjustments to sumproduct to ignore if all entries at each date is blank

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Exclamation Adjustments to sumproduct to ignore if all entries at each date is blank

    Hi there,

    I am struggling with a formula to count the number of non-blank cells in a range of dates, however, excluding dates that have ONLY blank entries (04-01-2019 in the workbook).

    Basically, I have a number of dates spread across the employees of 4 companies. For each company, I'd like to know how many employees were present over the period.

    A "blank entry" corresponds to an absent employee, whereas an "employee name" represents a present employee.

    Please see the enclosed sample workbook for a more detailed description.

    Thanks,
    Phil
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    I'm still not clear what results you expect. You say company D should be 100% since no employees were present on 4/2/2019, but there were 4 present on 5/1/2019.

    The formulae you have in I3:I6 are consistent so I'm struggling to understand why you think I6 is wrong when Company C in I5 is correct.

    Please manually add the results you expect and say how you have calculated them
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    Please try at I3

    =COUNTIFS($C$3:$C$32,H3,$D$3:$D$32,"*")/SUMPRODUCT((COUNTIFS($B$3:$B$32,$B$3:$B$32/(FREQUENCY($B$3:$B$32,$B$3:$B$32)>0),$C$3:$C$32,H3,$D$3:$D$32,"*")>0)*COUNTIFS($B$3:$B$32,$B$3:$B$32/(FREQUENCY($B$3:$B$32,$B$3:$B$32)>0),$C$3:$C$32,H3))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    @Bo_Ry - Excellent solution, thanks. However, the formula is very slow for larger datasets - is there any way to speed it up significantly?

    @Richard Buttrey - Please ignore the formulas in I3:I6 - the very thing I am looking for is a formula to replace those.

    The formula must ignore dates that have NO employees at all. So, for company D, the result should be 100% since no employees from company D were present on 04-02-2019 (and hence that date should not be included in the calculation).

    The results should be:
    - Company A: 87,5%
    - Company B: 100%
    - Company C: 71,4%
    - Company D: 100%
    Last edited by Phil123456789; 09-19-2019 at 04:54 AM.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    Helper column for list of date in
    Q2
    =AGGREGATE(15,6,$B$3:$B$32/($B$3:$B$32>MAX(Q$1:Q1)),1)

    I'm not sure which one is faster, please try and feedback
    Option A
    I3
    =COUNTIFS($C$3:$C$32,H3,$D$3:$D$32,"*")/SUMPRODUCT((COUNTIFS($B$3:$B$32,$Q$2:$Q$12,$C$3:$C$32,H3,$D$3:$D$32,"*")>0)*COUNTIFS($B$3:$B$32,$Q$2:$Q$12,$C$3:$C$32,H3))

    Option B
    J3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Press Ctrl+Shift+Enter
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    Thanks Bo-RY, definitely works more smoothly, especially option B.

    However, still a somewhat slow due to my large data set.

    I am thinking an alternative solution is simply to remove the dates that do not contain any employee entries at all and then use a simple Sumproduct.

    Do you have any ideas fora formula I can type in column E to return "delete" for those dates that should be deleted, as I will be updating the dataset quarterly and would prefer not to manually go through all rows.

    See the image below.

    Thanks
    Attached Images Attached Images
    Last edited by Phil123456789; 09-20-2019 at 04:49 AM.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    Thanks for the feedback,
    Please try at E3
    =IF(COUNTIFS($B$3:$B$32,B3,$C$3:$C$32,C3,$D$3:$D$32,"<>"),"","Delete")

  8. #8
    Registered User
    Join Date
    03-08-2019
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    57

    Re: Adjustments to sumproduct to ignore if all entries at each date is blank

    Excellent, worked perfectly.

    Thanks man, really appreciate your time

+ 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. SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries
    By sansai in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2019, 09:12 PM
  2. sumproduct formula to ignore blank values
    By Look, More, What in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-17-2018, 05:43 PM
  3. Sumproduct - ignore blank cells
    By pauldaddyadams in forum Excel General
    Replies: 3
    Last Post: 04-23-2014, 11:42 AM
  4. Ignore Blank Cells in SUMPRODUCT formula
    By taniwha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2013, 10:52 AM
  5. Sumproduct: how to ignore blank variables
    By robotlust in forum Excel General
    Replies: 5
    Last Post: 05-17-2012, 11:02 PM
  6. [SOLVED] How to get Sumproduct to ignore a criteria if it is blank?
    By skysurfer in forum Excel General
    Replies: 3
    Last Post: 04-29-2012, 04:54 PM
  7. Replies: 2
    Last Post: 04-28-2012, 05: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