To get the total closed for Brian just just the last two columns only.
closed & Brian

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
J
Yes we need to look at all three columns to get a true count of what the
employee processed for the day. Now using your new example that works in
this scenario but would create additional work as any one of the employees
could reassign the work from someone else to themselves thus I would have
to
create a formula that included everyone. So what would be the easiest way
to
go so that way it looks at Brian having 55 processed and then picking up
Sandra's 24 giving a total of 79. But the scenario we could have is Brian
processing his 55, 24 from Sandra and another 30 from Jennifer so his true
total count for the day would be 109.
--
Dot


"JLatham" wrote:

Dot, I think we have a disconnect regarding what and how to test to get
the
results you want.

The formula I put in there
=SUMPRODUCT(--(K2:K16="Brian"),--(L2:L16="closed"),--(M2:M16="Brian"))
looks at ALL THREE columns and only if the conditions in each of them is
true do you get a count of that row. So, the results of 3 and 0 are just
what I'd expect since only 3 rows have "brian | closed | brian" in them,
and
if you change the formula to
=SUMPRODUCT(--(K2:K16="Sandra"),--(L2:L16="closed"),--(M2:M16="Sandra"))
you will get zero because none of the entries in column M (Reassigned)
has
Sandra in it, at least not in the test data put up with the file.

We have 3 columns to look at, should we be looking at all 3 columns as
the
formula I put up does, or only two columns as either Assigned and Status
or
Status and Reassigned?

You can even check for combinations: this would give you total for those
originally Assigned to EITHER Brian or Sandra, that are closed and were
closed by Brian (but not by Sandra):
=SUMPRODUCT(--(K2:K16="Brian"),--(L2:L16="closed"),--(M2:M16="Brian")) +
SUMPRODUCT(--(K2:K16="Sandra"),--(L2:L16="closed"),--(M2:M16="Brian"))




"Dot" wrote:

Okay I copied your example down and using same data you provided I got
the
result of 2 for Brian and none for Sandra, where as there should be 3
for
Brian and 8 for Sandra. I'm using Excel 2003 on my home computer so
that
shouldn't matter should it? On my office laptop I also have Excel 2003
so I
should be getting the same answer on each using the formula provided
which I
am.
--
Dot


"JLatham" wrote:

Dot,
Try replacing the commas with * and see how it goes. It works fine
for me
as written in Excel 2007, but I didn't go back and try in any earlier
version
of Excel. To be frank, my 'expertise' with SUMPRODUCT() is virtually
non-existant, I don't use it much at all. Don and others are much
better
with it than I am, although I'm trying to rectify that shortcoming
since it
is such a handy tool, and it was kind of obvious to me that this was
a
situation where it would do the job for you if set up properly. I
may have
failed in the setup.

Oh, and NO - the -- were meant to be there, it is the commas that
appear to
be in doubt. What cell(s) are you putting your formula into?

I threw a quick test together using just 16 rows and you can grab the
file
and see if the formula will work on your system or not:
http://www.jlathamsite.com/uploads/SumproductForDot.xls
I put the formula at the end of column M, but you should be able to
put it
anywhere else on the sheet (other than in K2:M16) and it should still
work.


"Dot" wrote:

Jennifer is in column 1, I tried your function below but I'm
getting a
circular error, was your -- meant to be *? Another question, by
the way
Assigned is in column K, Status column L and Reassigned is in
column M, not
all of the rows are reassigned only after the individual finishes
their work
they help their team mates, so we can have some rows in column M
that are
blank. Not sure if this will have an impact but I tried putting the
persons
name their for what wasn't reassigned and it then doubled the total
so I
don't think I have to do that but maybe again account for a blank
cell? I
also checked for capitalization and all is in order.

--
Dot


"JLatham" wrote:

Jennifer?? Jennifer was not invited to the party.

Which column does Jennifer appear in? 1st, 2nd? 3rd?

=SUMPRODUCT(--(K2:K16="Brian"),--(L2:L16="closed"),--(M2:M16="Brian"))
works for me to total up the rows where Brian was assigned, the
issue is
closed and Brian was the closer. Spelling and capitalization
count. Change
16s to 1669 in your formula.

"Dot" wrote:

Okay I just found a flaw, turns out its counting the
information twice for
two different people. Below is how the columns are set up and
the functions
I'm using
Assigned Status Reassigned
Sandra Closed
Sandra
Sandra Closed
Sandra Closed
Sandra Closed brian
Sandra
Sandra Closed
Sandra Closed
Sandra Closed
Sandra Closed brian
Sandra Closed brian
Sandra Closed
Sandra
Sandra Closed
Sandra Closed Christine

Brian took over some of the assigned tasks that were given to
Sandra but it
is counting them for both employees. Here is the new function
I added, it
worked for one person but then put a N/A value on another:

=SUMPRODUCT((Assignments!K2:M1669="Brian")*(Assign ments!L2:L1669="closed")*(Assignments!M2:M669="Bri an"))

On Brian it is now giving me the #N/A value where on Jennifer
it worked.
--
Dot


"Don Guillett" wrote:

Glad to help

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
That worked!!! Thanks so much Don I've been working on
this all day and
it's
been driving me crazy :-)
--
Dot


"Don Guillett" wrote:

this maybe
=sumproduct((Assignments!K2:M1669="Brian")*(Assign ments!
n2:n1669="closed"))

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
I'm trying to bring three columns together that checks
for a condition
of
a
persons name and counts how many items were processed.
I have the
first
function =COUNTIF(Assignments!K2:M1669,"Brian") that's
working but I
don't
want it to include the blank cells only cells that have
"closed". What
information am I missing? I tried including the nested
argument If but
keep
getting a value error.
--
Dot