#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dot Dot is offline
external usenet poster
 
Posts: 5
Default Countif funtion

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dot Dot is offline
external usenet poster
 
Posts: 5
Default Countif funtion

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Countif funtion

I'm not sure which columns contain names, and which contain "closed" or what
is in third column at all. But this presumes names in K, "closed" in M -
nothing of consequence in L:
=SUMPRODUCT(--(Assignments!K2:K1669="Brian"),--(Assignments!M2:M1669="closed"))
should give count of entries with both Brian and closed on same row.


"Dot" wrote:

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

  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dot Dot is offline
external usenet poster
 
Posts: 5
Default Countif funtion

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






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Countif funtion

I'm confused about your layout.... Your example gives TWO columns.

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
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








  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Countif funtion

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






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dot Dot is offline
external usenet poster
 
Posts: 5
Default Countif funtion

Sorry Don,
Column K=Assigned, L=Status, M=Reassigned

I have 1700 lines that I'm working with and 18 employees, when I use the
below function the count no longer works but if I take off the last part of
the function it works but not correctly for everyone:

=SUMPRODUCT((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))

I made sure everyone starts with a capital letter on their name as well as
the status closed starting with a capital letter.

--
Dot


"Don Guillett" wrote:

I'm confused about your layout.... Your example gives TWO columns.

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
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









  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dot Dot is offline
external usenet poster
 
Posts: 5
Default Countif funtion

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








  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Countif funtion

I just tested this using
=SUMPRODUCT((K2:K16="B")*(L2:L16="C")*(M2:M16="B") )
b c b
b c b
b c c
b b b

For the above, I get 2. It will be true only if you get b,c,b on the same
row.
Send me your wb, if desired along with a complete explanation and what
errrors
And, for the info given below, I get 3 for Sandra,Closed,Brian
Capitalization does NOT matter.

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


..
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
Sorry Don,
Column K=Assigned, L=Status, M=Reassigned

I have 1700 lines that I'm working with and 18 employees, when I use the
below function the count no longer works but if I take off the last part
of
the function it works but not correctly for everyone:

=SUMPRODUCT((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))

I made sure everyone starts with a capital letter on their name as well as
the status closed starting with a capital letter.

--
Dot


"Don Guillett" wrote:

I'm confused about your layout.... Your example gives TWO columns.

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
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











  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

Thanks Don, I tried that as well as still getting a different answer. Here
is the scenario, I have 19 employees that are assigned tasks for the day of
applying cash and when they are finished with their work they go and help
their team mates. Take Brian for example, he was originally assigned 75
checks, of those checks he completed 55 then he went to another team member
and closed 24 of theirs. So on his original assignments he won't be putting
anything in the reassigned column but would be marking the items as closed,
so when I use the first set of function
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed) i
get the correct answer of 55, but the minute I add in
*(Assignments!M2:M1669="Brian")) it only shows 55 as it's not taking into
account the additional 24 he picked up, since they were originally assigned
to Sandy it is counting them in her numbers. If I change the first part of
the function to Assignments!K2:M1669="Brian")) then it gives me a figure of
134 which is adding the K column twice but also picking up the M column. I
have tried changing the operand of * to a minus/plus sign but that doesn't
work either. I thought maybe since I was doing this on another spread sheet
and bring the information in is why it was causing the issue but that is not
the case as I get the same information if I put on the same spread sheet as
where the data is being housed.

Your thoughts?
--
Dot


"Don Guillett" wrote:

I just tested this using
=SUMPRODUCT((K2:K16="B")*(L2:L16="C")*(M2:M16="B") )
b c b
b c b
b c c
b b b

For the above, I get 2. It will be true only if you get b,c,b on the same
row.
Send me your wb, if desired along with a complete explanation and what
errrors
And, for the info given below, I get 3 for Sandra,Closed,Brian
Capitalization does NOT matter.

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


..
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
Sorry Don,
Column K=Assigned, L=Status, M=Reassigned

I have 1700 lines that I'm working with and 18 employees, when I use the
below function the count no longer works but if I take off the last part
of
the function it works but not correctly for everyone:

=SUMPRODUCT((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))

I made sure everyone starts with a capital letter on their name as well as
the status closed starting with a capital letter.

--
Dot


"Don Guillett" wrote:

I'm confused about your layout.... Your example gives TWO columns.

--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
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












  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Countif funtion

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






  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

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






  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Countif funtion

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








  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

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






  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Countif funtion

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








  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Countif funtion

Yes, what Don just said above - the L and M portions of the formulas only for
all closed by brian (or who ever else you want by changing the criteria in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:

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






  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

okay I'm confused now, currently we have the formula showing as
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian")),
which currently gives me a count of 55 and I want the count of 79 so I need
to put it what way? And this would be the same for everyone?
--
Dot


"JLatham" wrote:

Yes, what Don just said above - the L and M portions of the formulas only for
all closed by brian (or who ever else you want by changing the criteria in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:

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






  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Countif funtion

If you want closed, brian total use
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian"))
or even
=countif(Assignments!M2:M1669,"Brian")
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
okay I'm confused now, currently we have the formula showing as
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian")),
which currently gives me a count of 55 and I want the count of 79 so I
need
to put it what way? And this would be the same for everyone?
--
Dot


"JLatham" wrote:

Yes, what Don just said above - the L and M portions of the formulas only
for
all closed by brian (or who ever else you want by changing the criteria
in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:

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










  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

Can we start back at the beginning please, I need to use all three columns in
order to come up with the total of closed items for all employees. When I
use
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))
I get the total 0, when I use
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian")) I
get the total 24 when I added in =countif(Assignments!M2:M1669,"Brian") I get
FALSE, so right now my formula is looking like
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian"))=COUNTIF(Assignments!M2:M 1669,"Brian"), what am I missing?
Dot


"Don Guillett" wrote:

If you want closed, brian total use
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian"))
or even
=countif(Assignments!M2:M1669,"Brian")
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...
okay I'm confused now, currently we have the formula showing as
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian")),
which currently gives me a count of 55 and I want the count of 79 so I
need
to put it what way? And this would be the same for everyone?
--
Dot


"JLatham" wrote:

Yes, what Don just said above - the L and M portions of the formulas only
for
all closed by brian (or who ever else you want by changing the criteria
in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:

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









  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,979
Default Countif funtion

To simplify the calculation, you could add a formula in column N, with
the heading "Closed By":

=IF(L2<"Closed","",IF(M2="",K2,M2))

This would calculate the name of the person who closed the file.
Then, count the names in that column, using a COUNTIF formula. For
example:
=COUNTIF(N2:N1669,P2)
Where P2 contains a name.

Dot wrote:
Can we start back at the beginning please, I need to use all three columns in
order to come up with the total of closed items for all employees. When I
use
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))
I get the total 0, when I use
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian")) I
get the total 24 when I added in =countif(Assignments!M2:M1669,"Brian") I get
FALSE, so right now my formula is looking like
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian"))=COUNTIF(Assignments!M2:M 1669,"Brian"), what am I missing?
Dot


"Don Guillett" wrote:


If you want closed, brian total use
=sumproduct((Assignments!L2:L1669="Closed")*(Ass ignments!M2:M1669="Brian"))
or even
=countif(Assignments!M2:M1669,"Brian")
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...

okay I'm confused now, currently we have the formula showing as
=sumproduct((Assignments!K2:K1669="Brian")*(Ass ignments!L2:L1669="Closed")*(Assignments!M2:M1669= "Brian")),
which currently gives me a count of 55 and I want the count of 79 so I
need
to put it what way? And this would be the same for everyone?
--
Dot


"JLatham" wrote:


Yes, what Don just said above - the L and M portions of the formulas only
for
all closed by brian (or who ever else you want by changing the criteria
in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:


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="Bria n")*(Assignments!L2:L1669="closed")*(Assignments!M 2:M669="Brian"))

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="B rian")*(Assignments!
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









--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dot dot is offline
external usenet poster
 
Posts: 29
Default Countif funtion

Debra you rock!!!! Thank you so much!!! That worked and everything is
falling place now. Thanks for everyone's help to figure this out I learned a
lot the last few days on using formulas!
--
Dot


"Debra Dalgleish" wrote:

To simplify the calculation, you could add a formula in column N, with
the heading "Closed By":

=IF(L2<"Closed","",IF(M2="",K2,M2))

This would calculate the name of the person who closed the file.
Then, count the names in that column, using a COUNTIF formula. For
example:
=COUNTIF(N2:N1669,P2)
Where P2 contains a name.

Dot wrote:
Can we start back at the beginning please, I need to use all three columns in
order to come up with the total of closed items for all employees. When I
use
=sumproduct((Assignments!K2:K1669="Brian")*(Assign ments!L2:L1669="Closed")*(Assignments!M2:M1669="Br ian"))
I get the total 0, when I use
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian")) I
get the total 24 when I added in =countif(Assignments!M2:M1669,"Brian") I get
FALSE, so right now my formula is looking like
=sumproduct((Assignments!L2:L1669="Closed")*(Assig nments!M2:M1669="Brian"))=COUNTIF(Assignments!M2:M 1669,"Brian"), what am I missing?
Dot


"Don Guillett" wrote:


If you want closed, brian total use
=sumproduct((Assignments!L2:L1669="Closed")*(Ass ignments!M2:M1669="Brian"))
or even
=countif(Assignments!M2:M1669,"Brian")
--
Don Guillett
SalesAid Software

"Dot" wrote in message
...

okay I'm confused now, currently we have the formula showing as
=sumproduct((Assignments!K2:K1669="Brian")*(Ass ignments!L2:L1669="Closed")*(Assignments!M2:M1669= "Brian")),
which currently gives me a count of 55 and I want the count of 79 so I
need
to put it what way? And this would be the same for everyone?
--
Dot


"JLatham" wrote:


Yes, what Don just said above - the L and M portions of the formulas only
for
all closed by brian (or who ever else you want by changing the criteria
in
the M part, as --(M2:M1669="Dot")

"Dot" wrote:


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="Bria n")*(Assignments!L2:L1669="closed")*(Assignments!M 2:M669="Brian"))

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="B rian")*(Assignments!
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.

  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,979
Default Countif funtion

You're welcome! Thanks for letting me know that it helped.

Dot wrote:
Debra you rock!!!! Thank you so much!!! That worked and everything is
falling place now. Thanks for everyone's help to figure this out I learned a
lot the last few days on using formulas!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Countif funtion

hi

i am trying to count cells in one column that match a criteria another column

Table A Table B Table C
Location Product Company

Ports Area A XYX co
Corniche area B ABC co
City area B DTS co
Village area C GHK co
Corniche area A PQR co
City area C ABC co

Here i know from the above table that there are two companies in "corniche
area" bu doing count but how do i get how many companies in 'corniche area '
that buy product A ?

regards

rajesh





















"Dot" wrote:

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



  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countif funtion

Try this...

Locations in column A
Products in column B

E1 = Corniche area
F1 = A

=SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1))

--
Biff
Microsoft Excel MVP


"rajesh.b." wrote in message
...
hi

i am trying to count cells in one column that match a criteria another
column

Table A Table B Table C
Location Product Company

Ports Area A XYX co
Corniche area B ABC co
City area B DTS co
Village area C GHK co
Corniche area A PQR co
City area C ABC co

Here i know from the above table that there are two companies in
"corniche
area" bu doing count but how do i get how many companies in 'corniche area
'
that buy product A ?

regards

rajesh





















"Dot" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif Formula /Sort Bug??? orcfodder Excel Discussion (Misc queries) 2 January 12th 06 11:04 AM
Formula format for Count or Countif funtion with two criterias Debi Excel Worksheet Functions 2 September 26th 05 08:23 PM
COUNTIF or not to COUNTIF on a range in another sheet Ellie Excel Worksheet Functions 4 September 15th 05 10:06 PM
Combining IF and COUNTIF based on two columns maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM
Countif - Countif maswinney Excel Worksheet Functions 3 November 16th 04 12:06 AM


All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"