Alternative to array formula

Can you post a small sanitized sample of data?

have you tried a sumproduct. it might be a bit quicker

Sumproduct.xlsx


the yellow boxes represent the filters you want. and the green box is where the formula is it might give you a good start. if you want a solution on your spreadsheet send a small sample and i can make it easily

Thanks guys,

Sorry for the slow reply ... I accidentally hit enter on the wrong cell yesterday and my PC spent the rest of the day running the formula on all of the cells.

I think a SUMPRODUCT may work, but I don't have a ton of experience using that function and wasn't able to get it working when i tried.

I've attached a sample workbook. The green column on the Analysis tab has the formula in question.

Test.xlsx

This would work for you I went to your data sheet and added "helper Rows" to pull data this way you can just use a max - min formula to get your results. I transposed your analysis table to make it able to drag the formula across if you have lots of workers.

Test-1.xlsx


let me know if this is what you are looking for

I like the concept you went with there. Not sure it is working the way i expect however. The "total days" would be the number of days where completed is >1. Not the number of days between the first day they had a unit complete and the last day. This is because the person in question might work on project A on Monday, and project B on Tuesday (etc). Where the analysis page would only want to know how many days were spent working on A.

Adrian, for example, only worked on A1B1 for two days (the 19th and the 23rd).

Modifying your concept, however, seems to work. If we use =COUNTIFS(PROJECT[RANGE],Project, PERSON[RANGE], Person, TIMESTAMP[RANGE],TIMESTAMP[RANGE]) , i get the expected value of "2" for "Adrian".

It also doesnt require the J through O columns you added in the Data tab. I'll try it on the actual and report back in a bit. Thanks, hopefully the idea works on the full data!

For some reason it isnt working in the real data. Using the TIMESTAMP(RANGE) as both the Range and the Condition results in an incorrect count of days. It appears, in this case, to just be counting the number of times one of the days appears in the data where the Person and Project conditions are met.

I've tried adding a frequency check to the data but this just results in the horribly long processing time and a value of zero. There has to be a solution I'm not thinking of that just returns the total number of days a project has data and meets the person/project conditions.

that is one of the issues with your data you dont have a start and an end to the work what i see is for every timestamp you have that is one days work? is that not the case?

Correct, I wouldnt have a start and an end date/time. This is just a record of a unit of work being completed, so what i have is a list of how many items were completed each day, what type (project) and by whom.

So in my 700k +/- records, there are about 30 separate projects and around 70 people. I'm trying to determine the average number of units completed on a project each day worked, by person. Since the staff can work on other projects, I'm only looking for days where they completed at least one unit. So on a project, person A may have worked 20 days and completed 200 units, where person B worked 10 days and completed 100 (each completing 10/day in this case).

Test-1.xlsxis there an in progress status? that would allow you to count the days worked on? i made an adjustment to see if that worked

Formula:

Please Login or Register  to view this content.

ok so this one should then count the number of "completed projects by a person filtered by the project name

Test-1.xlsx

here is what i think should be it. I added more criteria to make it look for only the days within the date range

Ok. I tried to start with a fresh approach using the DCOUNTA function, but get the same error with the formula counting EACH date that meets the other criteria, not the instances of a unique date. I'm completely confused as I thought counting unique was the entire purpose of the function.

Workbook attached. New formula is on the Data tab in Q2.

I highlighted each day a different color and turned all instances that dont meet the criteria red text to make it easy to spot the expected result. So in the test case, again, "2". One green (19th) and one yellow (23rd) for Adrian. Its counting both instances of the 19th, however.

Test-1-3.xlsx

Bump looking for help. I feel like we're pretty close, but my alternate attempts havent led to anything