+ 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
    33

    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
    Excel for Windows & Mac - all versions.
    Posts
    25,941

    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
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,801

    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
    33

    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
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,801

    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
    33

    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
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,801

    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
    33

    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)

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