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

1. ## 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

2. ## 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

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

=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))

4. ## 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%

5. ## 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:
`Please Login or Register  to view this content.`

Press Ctrl+Shift+Enter

6. ## 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

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

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

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

Excellent, worked perfectly.

Thanks man, really appreciate your time

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