+ 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
    2013
    Posts
    51

    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 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
    26,849

    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

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

  3. #3
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,106

    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
    2013
    Posts
    51

    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 Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,106

    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
    2013
    Posts
    51

    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 Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,106

    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
    2013
    Posts
    51

    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