26 reputation
13
bio website
location
age
visits member for 3 years, 10 months
seen Dec 6 at 22:46

Nov
10
awarded  Popular Question
Oct
20
awarded  Notable Question
Apr
3
awarded  Popular Question
Jul
22
comment Make COUNTIFS count duplicate values only once
The problem with the above function is that I can't extend it to a SUMIFS formula (which I want to.) In my real data set, each entry has other fields that I want to sum up without overreporting duplicates on the same controller. Thus, a function that lives entirely within the COUNTIFS/SUMIFS syntax would be ideal.
Jul
22
comment Make COUNTIFS count duplicate values only once
Also, I actually found a different way to report the numbers I want, but it exploits the fact that I know I only have a certain set of controllers: =SUMPRODUCT(--(COUNTIFS([Controller], Controllers[Name],[Start Timeslot],"<="&[@[Start Timeslot]],[End Timeslot],">"&[@[Start Timeslot]])>0)) . Controllers[Name] is a separate 1 column table that contains all the controllers. Then Excel will run a countifs for each possible controller. The SUMPRODUCT and --() wrapping will count a max of one for each controller.
Jul
22
comment Make COUNTIFS count duplicate values only once
The reason that SUMPRODUCT is itself in an array formula is because if I use an IFERROR inside of sumproduct, sumproduct fails to parse any arrays inside the IFERROR. You can see this by replacing the SUMPRODUCT column with a regular formula and stepping through it with "Evaluate Formula." Wrapping the whole thing up in an array formula seems to solve this, but I feel dirty doing it >_<
Jul
20
comment Make COUNTIFS count duplicate values only once
The overlap problem isn't occurring at the start and end of the timeslots; That's taken care of by the [end timeslot], ">"&[@[start timeslot]]. The overlap that concerns me is two tests running on controller 4 will cause the formula to count controller 4 twice if both tests run during another test's start time. I want to catch that controller 4 is being used at that time, but not overreport it.
Jul
20
asked Make COUNTIFS count duplicate values only once
Jul
7
awarded  Student
Jul
6
asked Pivoting concurrent start and end times into timeslots with Excel 2010 PivotTable