Home 
Search 
Today's Posts 
#12




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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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




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 nonexistant, 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Countif Formula /Sort Bug???  Excel Discussion (Misc queries)  
Formula format for Count or Countif funtion with two criterias  Excel Worksheet Functions  
COUNTIF or not to COUNTIF on a range in another sheet  Excel Worksheet Functions  
Combining IF and COUNTIF based on two columns  Excel Discussion (Misc queries)  
Countif  Countif  Excel Worksheet Functions 