| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct Q | Excel Worksheet Functions | |||
Hybrid Mode
