COUNTIF: 2 criteria: Date Range Column & Text Column

1. COUNTIF: 2 criteria: Date Range Column & Text Column

Col C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date
range in Col F.
Does anybody have an answer to this?

2. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

Sounds like a job for SUMPRODUCT but note you cannot reference the
entire column using this function.

Assume that A1 is the date you are using as criteria and A2 is the
value you are trying to find in Col F.

=SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))

How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
values where the "--" turns them into 1 for TRUE and 0 for FALSE.
F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
pair of 1's and 0's then adds them up. So if the first pair (C1 and
F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
second pair are both TRUE the result would be 1 x 1 or 1. Add up all
of the ones and you'll have your count.

- John
www.JohnMichl.com

3. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

=SUMPRODUCT(--(C2:C200="text"),--(F2:F200>=--"2005-01-01"),--(F2:F200<=--"20
05-01-31"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"MAC" <MAC@discussions.microsoft.com> wrote in message
news:A1B58BDE-ECDF-4F04-8E18-DEF0CAB4949B@microsoft.com...
> Col C = Text and Col F = dates
> I would like to count the # of times a value occurs in Col C based on a

date
> range in Col F.
> Does anybody have an answer to this?

4. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

Hey John, see Bob's solution below - you can reference the entire column.

MAC

"John Michl" wrote:

> Sounds like a job for SUMPRODUCT but note you cannot reference the
> entire column using this function.
>
> Assume that A1 is the date you are using as criteria and A2 is the
> value you are trying to find in Col F.
>
> =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))
>
> How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
> values where the "--" turns them into 1 for TRUE and 0 for FALSE.
> F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
> pair of 1's and 0's then adds them up. So if the first pair (C1 and
> F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
> second pair are both TRUE the result would be 1 x 1 or 1. Add up all
> of the ones and you'll have your count.
>
> - John
> www.JohnMichl.com
>
>

5. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

That did it! Thanks Bob!

"Bob Phillips" wrote:

> =SUMPRODUCT(--(C2:C200="text"),--(F2:F200>=--"2005-01-01"),--(F2:F200<=--"20
> 05-01-31"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "MAC" <MAC@discussions.microsoft.com> wrote in message
> news:A1B58BDE-ECDF-4F04-8E18-DEF0CAB4949B@microsoft.com...
> > Col C = Text and Col F = dates
> > I would like to count the # of times a value occurs in Col C based on a

> date
> > range in Col F.
> > Does anybody have an answer to this?

>
>
>

6. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

Bob is not referencing the entire column he is referencing the range
from row 2 through row 200. The entire column would be represented by
\$C:\$C. This is not permitted in SUMPRODUCT.

- John

7. Re: COUNTIF: 2 criteria: Date Range Column & Text Column

I am doing something similar. I have a column (AX) full of several different
texts. These texts are OPEN, CLOSED, WORKING ... I have another column
with dates (B). I want to have all the data on the 1st worksheet. I want to
have 4 subsequent worksheets, one for each quarter of the year. I want
worksheet 2 to give me a total of open cases between 1-1-2005 and 3-31-2005.
Then another for total closed and total working....etc.

I tried to use this formula, but it returns a value of zero.....

=SUMPRODUCT(--(AX2:AX61="OPEN"),--(B2:B61>=--"2005-10-01"),--(B2:B61<=--"2005-12-31"))

Do I have something messed uP?

"MAC" wrote:

> That did it! Thanks Bob!
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(C2:C200="text"),--(F2:F200>=--"2005-01-01"),--(F2:F200<=--"20
> > 05-01-31"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MAC" <MAC@discussions.microsoft.com> wrote in message
> > news:A1B58BDE-ECDF-4F04-8E18-DEF0CAB4949B@microsoft.com...
> > > Col C = Text and Col F = dates
> > > I would like to count the # of times a value occurs in Col C based on a

> > date
> > > range in Col F.
> > > Does anybody have an answer to this?

> >
> >
> >

8. COUNTIF date falls within a certain month

I have a similar problem but I am not familiar with SUMPRODUCT.
I have a range of dates and I want to get a count of cells by month. (How
many January, February, etc.)

So, if A1:A5 is

1/2/2006
2/2/2006
3/2/2006
3/5/2006
4/2/2006

....and if I'm looking for the number of dates in March I want to...

COUNTIF(A1:A5, >= "3/1/2006" AND < "4/1/2006")

(...but of course this formula doesn't work.)

So, how would I do it?

Thanks,
Scott

"John Michl" <waxwing1@gmail.com> wrote in message
news:1134064848.470234.238320@g47g2000cwa.googlegroups.com...
> Sounds like a job for SUMPRODUCT but note you cannot reference the
> entire column using this function.
>
> Assume that A1 is the date you are using as criteria and A2 is the
> value you are trying to find in Col F.
>
> =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))
>
> How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
> values where the "--" turns them into 1 for TRUE and 0 for FALSE.
> F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
> pair of 1's and 0's then adds them up. So if the first pair (C1 and
> F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
> second pair are both TRUE the result would be 1 x 1 or 1. Add up all
> of the ones and you'll have your count.
>
> - John
> www.JohnMichl.com
>

9. Re: COUNTIF date falls within a certain month

=countif(a1:a5,">="&date(2006,3,1)) - countif(a1:a5,">="&date(2006,4,1))

is one way.

Scott Lolmaugh wrote:
>
> I have a similar problem but I am not familiar with SUMPRODUCT.
> I have a range of dates and I want to get a count of cells by month. (How
> many January, February, etc.)
>
> So, if A1:A5 is
>
> 1/2/2006
> 2/2/2006
> 3/2/2006
> 3/5/2006
> 4/2/2006
>
> ...and if I'm looking for the number of dates in March I want to...
>
> COUNTIF(A1:A5, >= "3/1/2006" AND < "4/1/2006")
>
> (...but of course this formula doesn't work.)
>
> So, how would I do it?
>
> Thanks,
> Scott
>
> "John Michl" <waxwing1@gmail.com> wrote in message
> news:1134064848.470234.238320@g47g2000cwa.googlegroups.com...
> > Sounds like a job for SUMPRODUCT but note you cannot reference the
> > entire column using this function.
> >
> > Assume that A1 is the date you are using as criteria and A2 is the
> > value you are trying to find in Col F.
> >
> > =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))
> >
> > How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
> > values where the "--" turns them into 1 for TRUE and 0 for FALSE.
> > F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
> > pair of 1's and 0's then adds them up. So if the first pair (C1 and
> > F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
> > second pair are both TRUE the result would be 1 x 1 or 1. Add up all
> > of the ones and you'll have your count.
> >
> > - John
> > www.JohnMichl.com
> >

--

Dave Peterson

10. Jeremy,

=SUMPRODUCT(--(AX2:AX61="OPEN"),--(B2:B61>=DATE(2005,10,1)),--(B2:B61<=DATE(2005,12,31)))

or put your reference dates in two other cells say F1 & G1

=SUMPRODUCT(--(AX1:AX20="OPEN"),--(B1:B20>=F1),--(B1:B20<=G1))

or don't use the "--" at all

=SUMPRODUCT((AX1:AX20="OPEN")*(B1:B20>=F1)*(B1:B20<=G1))

You were putting the "--" after the = sign in your conditions, this is not necessary. Also, having the dates in " " doesn't seem to work either.

HTH

Steve

11. Jeremy,

Sorry, forgot to change the ranges.

=SUMPRODUCT((AX2:AX61="OPEN")*(B2:B61>=F1)*(B2:B61<=G1))

Steve

12. Re: COUNTIF date falls within a certain month

Perfect! Works like a charm.
Thanks!!
Scott

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43E39F32.BE6CE23@verizonXSPAM.net...
> =countif(a1:a5,">="&date(2006,3,1)) - countif(a1:a5,">="&date(2006,4,1))
>
> is one way.
>
> Scott Lolmaugh wrote:
>>
>> I have a similar problem but I am not familiar with SUMPRODUCT.
>> I have a range of dates and I want to get a count of cells by month.
>> (How
>> many January, February, etc.)
>>
>> So, if A1:A5 is
>>
>> 1/2/2006
>> 2/2/2006
>> 3/2/2006
>> 3/5/2006
>> 4/2/2006
>>
>> ...and if I'm looking for the number of dates in March I want to...
>>
>> COUNTIF(A1:A5, >= "3/1/2006" AND < "4/1/2006")
>>
>> (...but of course this formula doesn't work.)
>>
>> So, how would I do it?
>>
>> Thanks,
>> Scott
>>
>> "John Michl" <waxwing1@gmail.com> wrote in message
>> news:1134064848.470234.238320@g47g2000cwa.googlegroups.com...
>> > Sounds like a job for SUMPRODUCT but note you cannot reference the
>> > entire column using this function.
>> >
>> > Assume that A1 is the date you are using as criteria and A2 is the
>> > value you are trying to find in Col F.
>> >
>> > =SUMPRODUCT(--(C1:C10=A1)*(--(F1:F10=A2)))
>> >
>> > How does this work? C1:C10=A1 creates an array of ten TRUE or FALSE
>> > values where the "--" turns them into 1 for TRUE and 0 for FALSE.
>> > F1:F10=A2 does the same for the other range. SUMPRODUCT multiples each
>> > pair of 1's and 0's then adds them up. So if the first pair (C1 and
>> > F1) evaluate as FALSE and TRUE, the results is 0 x 1 or 0. If the
>> > second pair are both TRUE the result would be 1 x 1 or 1. Add up all
>> > of the ones and you'll have your count.
>> >
>> > - John
>> > www.JohnMichl.com
>> >

>
> --
>
> Dave Peterson

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1