Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
=COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR")
The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
Actually the range I am trying to look at is G4:G10/H4:H10/J4:J10/and K4:K10.
Each column has a different responsibility held by the person and the responsibilites are assigned to varying functions defined by what they fall under Compliance, Underwriting, and Other. As I am reading through here I am wondering if SUMPRODUCT is not what I need. Any help would be appreciated. Thanks. -- Deb "Deb" wrote: =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
Please don't post more than once, stay in the original thread.
The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
Sorry, I was hoping for someone to respond so I went to another catagory.
I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
What is the result you get, if I create a dummy sheet and just put in "DS"
in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
My spreadsheet is showing "4".
-- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero?
-- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
Thanks for all your help on this.
Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
You certainly did, I somehow missed the J10. Try this
=SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMP RODUCT(($D$4:$D$10="DS")*($J$4:$K$100)) I missed the second 0 in my original formula, this shows 3 now -- Regards, Peo Sjoblom "Deb" wrote in message ... Thanks for all your help on this. Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
That does help, but I need to only show 2. If multiple exceptions appear on
the same row I still only want to count it as 1 to make it calculate 2/2=100%. Is there another conditional statement I could add? -- Deb "Peo Sjoblom" wrote: You certainly did, I somehow missed the J10. Try this =SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMP RODUCT(($D$4:$D$10="DS")*($J$4:$K$100)) I missed the second 0 in my original formula, this shows 3 now -- Regards, Peo Sjoblom "Deb" wrote in message ... Thanks for all your help on this. Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
There might be something fairly complicated, I'll post back when I have
played a round a bit and if I find something. However it is not the best of spreadsheet designs. You could easily solve by doing this Put this formula somewhere off the screen in row4 SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40)) copy down to and including row 10 (so if you put it in Z4 then copy it down to Z10) then just use this formula =SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100)) which will return 2 -- Regards, Peo Sjoblom "Deb" wrote in message ... That does help, but I need to only show 2. If multiple exceptions appear on the same row I still only want to count it as 1 to make it calculate 2/2=100%. Is there another conditional statement I could add? -- Deb "Peo Sjoblom" wrote: You certainly did, I somehow missed the J10. Try this =SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMP RODUCT(($D$4:$D$10="DS")*($J$4:$K$100)) I missed the second 0 in my original formula, this shows 3 now -- Regards, Peo Sjoblom "Deb" wrote in message ... Thanks for all your help on this. Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
I was hoping there would be something fairly simple.
What is this actually doing? I am not familiar with the "MOD" and what is the 3 doing? SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40)) So is there no way to say if "DS" and the range is 1=1? -- Deb "Peo Sjoblom" wrote: There might be something fairly complicated, I'll post back when I have played a round a bit and if I find something. However it is not the best of spreadsheet designs. You could easily solve by doing this Put this formula somewhere off the screen in row4 SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40)) copy down to and including row 10 (so if you put it in Z4 then copy it down to Z10) then just use this formula =SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100)) which will return 2 -- Regards, Peo Sjoblom "Deb" wrote in message ... That does help, but I need to only show 2. If multiple exceptions appear on the same row I still only want to count it as 1 to make it calculate 2/2=100%. Is there another conditional statement I could add? -- Deb "Peo Sjoblom" wrote: You certainly did, I somehow missed the J10. Try this =SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMP RODUCT(($D$4:$D$10="DS")*($J$4:$K$100)) I missed the second 0 in my original formula, this shows 3 now -- Regards, Peo Sjoblom "Deb" wrote in message ... Thanks for all your help on this. Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF help
It jumps over column I, if you only have text in I and no numbers you can
use =COUNTIF(G4:K4,"0") and copy down to row 10, what it will do is to count the values in these rows and if there are 4 values 0 in G4, H4, J4 and K4 it will return 4 but used in this formula =SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100)) (I assume you put the formula in Z4 and copied down to Z10) it will only count that as one occasion and not 4 if D4 contains "DS" -- Regards, Peo Sjoblom "Deb" wrote in message ... I was hoping there would be something fairly simple. What is this actually doing? I am not familiar with the "MOD" and what is the 3 doing? SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40)) So is there no way to say if "DS" and the range is 1=1? -- Deb "Peo Sjoblom" wrote: There might be something fairly complicated, I'll post back when I have played a round a bit and if I find something. However it is not the best of spreadsheet designs. You could easily solve by doing this Put this formula somewhere off the screen in row4 SUMPRODUCT((MOD(COLUMN(G4:K4),3)0)*(G4:K40)) copy down to and including row 10 (so if you put it in Z4 then copy it down to Z10) then just use this formula =SUMPRODUCT(--(D4:D10="DS"),--(Z4:Z100)) which will return 2 -- Regards, Peo Sjoblom "Deb" wrote in message ... That does help, but I need to only show 2. If multiple exceptions appear on the same row I still only want to count it as 1 to make it calculate 2/2=100%. Is there another conditional statement I could add? -- Deb "Peo Sjoblom" wrote: You certainly did, I somehow missed the J10. Try this =SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMP RODUCT(($D$4:$D$10="DS")*($J$4:$K$100)) I missed the second 0 in my original formula, this shows 3 now -- Regards, Peo Sjoblom "Deb" wrote in message ... Thanks for all your help on this. Yes. I think I had stated earlier that I had a 2 in J10. -- Deb "Peo Sjoblom" wrote: Do you have anything in G5, G10, J5, J10, K5, K10 that is greater than zero? -- Regards, Peo Sjoblom "Deb" wrote in message ... My spreadsheet is showing "4". -- Deb "Peo Sjoblom" wrote: What is the result you get, if I create a dummy sheet and just put in "DS" in D5 and D10 and 1 in H5 and 2 in H10 I get the result of 2 which I assume you want. If you get something else than you might have extra spaces in D etc. =SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($G$4:$H$100) )+SUMPRODUCT((TRIM($D$4:$D$10)="DS")*($J$4:$K$10)) -- Regards, Peo Sjoblom "Deb" wrote in message ... Sorry, I was hoping for someone to respond so I went to another catagory. I tried your suggestion and am getting a value but it is not the one I was hoping for. Below is the formula. SUMPRODUCT(($D$4:$D$10="DS")*($G$4:$H$100))+SUMPR ODUCT(($D$4:$D$10="DS")*($J$4:$K$10)) The results are that "DS" occurs 2x on D5 and D10 and H5=1 and H10=2 and J10=2. I am trying to cound the number of times the exceptions are "0" for each loan that person processed. Each row is a loan. The errors are defined in a range of G4:H10 and J4:K10. I then will divide the number of occurences per the number of loans that "DS" processed - which in the case above should be 2/2 = 100% Error rate. Therefore, if they processed 5 loans and only 2 loans had Compliance exceptions it would be 2/5=40% error rate. -- Deb "Peo Sjoblom" wrote: Please don't post more than once, stay in the original thread. The regulars will find your post As I posted to your other post you cannot use that function with ranges that are not equal in size You can try =SUMPRODUCT((D4:D10="CR")*(MOD(COLUMN(G4:K10),3)0 )*(G4:K100)) or =SUMPRODUCT((D4:D13="CR")*(G4:H130))+SUMPRODUCT(( D4:D13="CR")*(J4:K130)) -- Regards, Peo Sjoblom "Deb" wrote in message ... =COUNTIFS(G$4:H$10,"0",D$4:D$10,"CR")+COUNTIFS(J$ 4:K$10,"0",D$4:D$10,"CR") The above calculation does not work. Can anyone help me? I need to count the number of exceptions in a range of columns skipping column "I" if the persons initials in a range in column "D" equals their initials. Thanks. -- Deb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |