|
sumproduct
I have a question:
I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? |
sumproduct
=SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1"))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? |
sumproduct
Empty cells will be treated as being in January, too.
=SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson |
sumproduct
I tried both formula and they are giving me a #VALUE result. Does it have
anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson |
sumproduct
#VALUE sounds as if the range in your formula are not the same size, they
must be. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson |
sumproduct
Or maybe you have #value! errors in one of those ranges?
FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson |
sumproduct
What will be the formula if the B column also has an empty cells. I still get
the #VALUE. Thanks. "Bob Phillips" wrote: #VALUE sounds as if the range in your formula are not the same size, they must be. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson |
sumproduct
Yes, 2 of the cells in the range have the word VOID instead of the date. And
I want to keep the word VOID in there. How will the formula look like? Thanks. "Dave Peterson" wrote: Or maybe you have #value! errors in one of those ranges? FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson |
sumproduct
=SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1"))
becomes =SUM(--(IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEFT(B1: B100,1)="1")) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. FPJ wrote: Yes, 2 of the cells in the range have the word VOID instead of the date. And I want to keep the word VOID in there. How will the formula look like? Thanks. "Dave Peterson" wrote: Or maybe you have #value! errors in one of those ranges? FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
sumproduct
And you don't need the -- stuff:
=SUM((IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEF T(B1:B100,1)="1")) Dave Peterson wrote: =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) becomes =SUM(--(IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEFT(B1: B100,1)="1")) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. FPJ wrote: Yes, 2 of the cells in the range have the word VOID instead of the date. And I want to keep the word VOID in there. How will the formula look like? Thanks. "Dave Peterson" wrote: Or maybe you have #value! errors in one of those ranges? FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
sumproduct
IT WORKS!!!!!!! Thank you very much!!!! I just have one more question: What
does the number 1 in (LEFT(B1:B100,1) stands for? "Dave Peterson" wrote: And you don't need the -- stuff: =SUM((IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEF T(B1:B100,1)="1")) Dave Peterson wrote: =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) becomes =SUM(--(IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEFT(B1: B100,1)="1")) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. FPJ wrote: Yes, 2 of the cells in the range have the word VOID instead of the date. And I want to keep the word VOID in there. How will the formula look like? Thanks. "Dave Peterson" wrote: Or maybe you have #value! errors in one of those ranges? FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
sumproduct
=left(a1,14)
will return the leftmost 14 characters. =left(a1,1) will return the leftmost character. If you don't put a number in that second argument, excel will know that you want just 1 character. But I like being explicit. FPJ wrote: IT WORKS!!!!!!! Thank you very much!!!! I just have one more question: What does the number 1 in (LEFT(B1:B100,1) stands for? "Dave Peterson" wrote: And you don't need the -- stuff: =SUM((IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEF T(B1:B100,1)="1")) Dave Peterson wrote: =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) becomes =SUM(--(IF(ISNUMBER(A1:A100),MONTH(A1:A100))=1)*(LEFT(B1: B100,1)="1")) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. FPJ wrote: Yes, 2 of the cells in the range have the word VOID instead of the date. And I want to keep the word VOID in there. How will the formula look like? Thanks. "Dave Peterson" wrote: Or maybe you have #value! errors in one of those ranges? FPJ wrote: I tried both formula and they are giving me a #VALUE result. Does it have anything to do with the formula being linked to another worksheet? "Dave Peterson" wrote: Empty cells will be treated as being in January, too. =SUMPRODUCT(--(a1:a100<""),--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) is one way to avoid that problem. Bob Phillips wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(LEFT(B1:B100,1)="1")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "FPJ" wrote in message ... I have a question: I have a worksheet that in one column contains the dates of the reports were generated and in another column text codes (example: 1-UCF, 1-DEL, 1-HOM, 1-PAS, 2-UCF, 2-DEL, 2-HOM, 2-PAS). My question is, how do you write the formula if you want to count the number of codes that have the prefix 1 that occured on January? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com