Could anyone help me with the following:
I have three colums of data.
In the first colum I have names
The second Column I have week numbers
And the 3rd column I have dates
I have to report how many dates in ta given week number reports were submitted by each person.
The problem is that on some days more than one report has been submitted so when I count using an array formula such as
=SUM((A2:A10="Name")*(B2:B10="week number")
*C2:C10)
The result is wrong.
I need to count the number of distinct (unique) dates in the date column (c)for a given person on a given date
so I need a combination of
=SUM((A2:A10="Name")*(B2:B10="week number")
to select the ranges together with
=SUM(N(FREQUENCY(C1:A10,C1:C10)>0))
but I'm not sure how to put them together.
to count the unique entries for the range.
Or perhaps there is an easier way or another function I could use.
Any suggestions would be gratefully received.
I have three colums of data.
In the first colum I have names
The second Column I have week numbers
And the 3rd column I have dates
I have to report how many dates in ta given week number reports were submitted by each person.
The problem is that on some days more than one report has been submitted so when I count using an array formula such as
=SUM((A2:A10="Name")*(B2:B10="week number")
*C2:C10)
The result is wrong.
I need to count the number of distinct (unique) dates in the date column (c)for a given person on a given date
so I need a combination of
=SUM((A2:A10="Name")*(B2:B10="week number")
to select the ranges together with
=SUM(N(FREQUENCY(C1:A10,C1:C10)>0))
but I'm not sure how to put them together.
to count the unique entries for the range.
Or perhaps there is an easier way or another function I could use.
Any suggestions would be gratefully received.