Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count OR Sum Or if
I need to calculate the number of times an item appears within a date range.
Another words, if column A is contains my dates ranging from 1/1/05 - 7/1/05 and column B contains various symbols (ie A, B, C etc.), I need to calculate how many A's appear between 3/1/05 - 3/31/05. Is ther a formula to use? Or is there a way to do a combination IF/THEN statement? (If Column A is btwn 3/1/05-3/31/05 then count "A" that appear in column B) I am using Excel 2000. PLEASE HELP. My brain is goingo n overload! |
#2
|
|||
|
|||
Hi Diane1477,
Try something like this: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(A1:A100)="A")) or =SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(A1:A100)="A")) Regards, KL "Diane1477" wrote in message ... I need to calculate the number of times an item appears within a date range. Another words, if column A is contains my dates ranging from 1/1/05 - 7/1/05 and column B contains various symbols (ie A, B, C etc.), I need to calculate how many A's appear between 3/1/05 - 3/31/05. Is ther a formula to use? Or is there a way to do a combination IF/THEN statement? (If Column A is btwn 3/1/05-3/31/05 then count "A" that appear in column B) I am using Excel 2000. PLEASE HELP. My brain is goingo n overload! |
#3
|
|||
|
|||
Sorry - misprint. The formulae should read as follows:
=SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A")) or =SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(B1:B100)="A"))KL"KL" wrote in .. . Hi Diane1477, Try something like this: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(A1:A100)="A")) or=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100 <=DATE(2005,3,31))*(UPPER(A1:A100)="A")) Regards, KL "Diane1477" wrote in ...I need to calculate the number of times an item appears within a daterange. Another words, if column A is contains my dates ranging from 1/1/05 -7/1/05 and column B contains various symbols (ie A, B, C etc.), I need tocalculate how many A's appear between 3/1/05 - 3/31/05. Is ther a formula to use?Or is there a way to do a combination IF/THEN statement? (If Column A isbtwn 3/1/05-3/31/05 then count "A" that appear in column B) I am using Excel2000. PLEASE HELP. My brain is goingo n overload! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count OR Sum Or if
I had a similar need and the this formula worked great. I do have a couple of
questions: 1. Can you have the formula look at two conditions to meet before it counts. Were there would be (UPPER (B1:B100="A") and (UPPER (C1:C100="B")? 2. The "*" seems to be a form of a seperator, is that correct? It seems to me that it's similar to using commas, am I correct in that assumption? sdm "KL" wrote: Sorry - misprint. The formulae should read as follows: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A")) or =SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(B1:B100)="A"))KL"KL" wrote in .. . Hi Diane1477, Try something like this: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(A1:A100)="A")) or=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100 <=DATE(2005,3,31))*(UPPER(A1:A100)="A")) Regards, KL "Diane1477" wrote in ...I need to calculate the number of times an item appears within a daterange. Another words, if column A is contains my dates ranging from 1/1/05 -7/1/05 and column B contains various symbols (ie A, B, C etc.), I need tocalculate how many A's appear between 3/1/05 - 3/31/05. Is ther a formula to use?Or is there a way to do a combination IF/THEN statement? (If Column A isbtwn 3/1/05-3/31/05 then count "A" that appear in column B) I am using Excel2000. PLEASE HELP. My brain is goingo n overload! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count OR Sum Or if
Hi!
For question 1: Just add another array to the formula: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A")*(UPPER (C1:C100="B"))) For question 2: The "*" is the multiplication operator. What the formula is doing is multiplying all the arrays together like this: TRUE * FALSE * FALSE * FALSE = 0 TRUE * TRUE * TRUE * TRUE =1 FALSE * FALSE * FALSE * FALSE = 0 Then summing the result of the multiplication to arrive at a count. Biff "SDM" wrote in message ... I had a similar need and the this formula worked great. I do have a couple of questions: 1. Can you have the formula look at two conditions to meet before it counts. Were there would be (UPPER (B1:B100="A") and (UPPER (C1:C100="B")? 2. The "*" seems to be a form of a seperator, is that correct? It seems to me that it's similar to using commas, am I correct in that assumption? sdm "KL" wrote: Sorry - misprint. The formulae should read as follows: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A")) or =SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(B1:B100)="A"))KL"KL" wrote in .. . Hi Diane1477, Try something like this: =SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(A1:A100)="A")) or=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100 <=DATE(2005,3,31))*(UPPER(A1:A100)="A")) Regards, KL "Diane1477" wrote in ...I need to calculate the number of times an item appears within a daterange. Another words, if column A is contains my dates ranging from 1/1/05 -7/1/05 and column B contains various symbols (ie A, B, C etc.), I need tocalculate how many A's appear between 3/1/05 - 3/31/05. Is ther a formula to use?Or is there a way to do a combination IF/THEN statement? (If Column A isbtwn 3/1/05-3/31/05 then count "A" that appear in column B) I am using Excel2000. PLEASE HELP. My brain is goingo n overload! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions |