Count OR Sum Or if

D

Diane1477

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!
 
K

KL

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
 
K

KL

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<=DATE(2005,3,31))*(UPPER(B1:B100)="A"))KL"KL" <[email protected]> wrote in messageHi 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" <[email protected]> wrote in messageneed 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!>>
 
S

SDM

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 said:
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<=DATE(2005,3,31))*(UPPER(B1:B100)="A"))KL"KL" <[email protected]> wrote in messagenews:[email protected]...> 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" <[email protected]> wrote in messagenews:[email protected]...>>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
Click to expand...
am using Excel2000.>>>> PLEASE HELP. My brain is goingo n overload!>>
 
B

Biff

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
 
Top