SUMPRODUCT using an absolute value or a named variable

[Joe Mac]

All...

I'm looking to use the SUMPRODUCT to present back the a percentage of
transactions that are tied to a specific user... the challenge is that the
user can be selected from a dropdown list and populated in a specific named
cell address the name for the cell is RptEmployee... ideally the manager can
select any unique user from the dropdown list and generate the report based
on the users specific data
here is the formular which is pulling all of the data and making the
specific selection on the (RptEmployee=RptEmployee) segment... any help is
greatly appreciated in advance-

=SUMPRODUCT((CaseAcceptance="N")*(CaseMonth="July")*
(RptEmployee=RptEmployee)*(Counter))/COUNTIF(CaseMonth,"=July"))

 

[T. Valko]

(RptEmployee=RptEmployee)

If RptEmployee is a cell that contains your drop down then that expression
should be something like this:

(some_range=RptEmployee)
(A1:A100=RptEmployee)

 

[Joe Mac]

Thanks - I noticed in my formular that I was using the same RptEmployee name
definition to represent a range and the cell containing the dropdown... so I
change the formular to now read

=SUMPRODUCT((CaseAcceptance="N")*(CaseMonth="July")*
(Employee=RptEmployee)*(Counter))/COUNTIF(CaseMonth,"=July"))

Where Employee is the column containing the Employee name in the Source Data
....

The results are still not what I expect, but I have to look a bit deeper...
it on initial view doesn't appear to be picking up all values that I expect