# countif, sumproduct

1. ## countif, sumproduct

table:
1.5.2006 blue
2.5.2006 green
30.4.2005 green
21.5.2006 yellow
21.6.2006 blue

how can I count all the green guys with the datum after 1.5.2005?
countif will not probably work...

2. ## Re: countif, sumproduct

I will assume the first column are dates
With dates in column A and colours in B
=SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" <mg@discussions.microsoft.com> wrote in message
news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
> table:
> 1.5.2006 blue
> 2.5.2006 green
> 30.4.2005 green
> 21.5.2006 yellow
> 21.6.2006 blue
>
> how can I count all the green guys with the datum after 1.5.2005?
> countif will not probably work...
>

3. Hi,

Just out of interest more than anything but what is the signifcance of the "--" that appears before each argument as opposed to "*", that Excel Help puts between the two arguments?

Cheers,

Originally Posted by Bernard Liengme
I will assume the first column are dates
With dates in column A and colours in B
=SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" <mg@discussions.microsoft.com> wrote in message
news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
> table:
> 1.5.2006 blue
> 2.5.2006 green
> 30.4.2005 green
> 21.5.2006 yellow
> 21.6.2006 blue
>
> how can I count all the green guys with the datum after 1.5.2005?
> countif will not probably work...
>

4. ## Re: countif, sumproduct

thanx a lot, that's exactly what I have been looking for.
You've helped me a lot.

Bernard Liengme pÃ*Å¡e:

> I will assume the first column are dates
> With dates in column A and colours in B
> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "mg" <mg@discussions.microsoft.com> wrote in message
> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
> > table:
> > 1.5.2006 blue
> > 2.5.2006 green
> > 30.4.2005 green
> > 21.5.2006 yellow
> > 21.6.2006 blue
> >
> > how can I count all the green guys with the datum after 1.5.2005?
> > countif will not probably work...
> >

>
>
>

5. ## Re: countif, sumproduct

what if i need to use a range in the date f.e. from 1.4.2005 to 2.5.2005?

Bernard Liengme pÃ*Å¡e:

> I will assume the first column are dates
> With dates in column A and colours in B
> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "mg" <mg@discussions.microsoft.com> wrote in message
> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
> > table:
> > 1.5.2006 blue
> > 2.5.2006 green
> > 30.4.2005 green
> > 21.5.2006 yellow
> > 21.6.2006 blue
> >
> > how can I count all the green guys with the datum after 1.5.2005?
> > countif will not probably work...
> >

>
>
>

6. ## Re: countif, sumproduct

Hi

The -- coerces a TRUE result into a 1, and a FALSE result into a 0. This can
then be used to calculate with.
If you type =FALSE in a cell, it will give the result of FALSE. If you type
=--FALSE into a cell, you will get 0 as the result. Other ways of doing the
same job are to use a * to multiply or even a +0. It's just a way of making
Excel treat logical results as numbers.

Hope this helps.
--
Andy.

"giantwolf" <giantwolf.1rhs2p_1120223313.6087@excelforum-nospam.com> wrote
in message news:giantwolf.1rhs2p_1120223313.6087@excelforum-nospam.com...
>
> Hi,
>
> Just out of interest more than anything but what is the signifcance of
> the "--" that appears before each argument as opposed to "*", that
> Excel Help puts between the two arguments?
>
> Cheers,
>
>
>
>
> Bernard Liengme Wrote:
>> I will assume the first column are dates
>> With dates in column A and colours in B
>> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "mg" <mg@discussions.microsoft.com> wrote in message
>> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
>> > table:
>> > 1.5.2006 blue
>> > 2.5.2006 green
>> > 30.4.2005 green
>> > 21.5.2006 yellow
>> > 21.6.2006 blue
>> >
>> > how can I count all the green guys with the datum after 1.5.2005?
>> > countif will not probably work...
>> >

>
>
> --
> giantwolf
> ------------------------------------------------------------------------
> giantwolf's Profile:
> http://www.excelforum.com/member.php...o&userid=24718
>

7. ## Re: countif, sumproduct

Hi

Try something like this:
=SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="green"))

--
Andy.

"mg" <mg@discussions.microsoft.com> wrote in message
news:44CF5C68-B259-4BE8-B66D-C5CED941BA0D@microsoft.com...
> what if i need to use a range in the date f.e. from 1.4.2005 to 2.5.2005?
>
> Bernard Liengme píse:
>
>> I will assume the first column are dates
>> With dates in column A and colours in B
>> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "mg" <mg@discussions.microsoft.com> wrote in message
>> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
>> > table:
>> > 1.5.2006 blue
>> > 2.5.2006 green
>> > 30.4.2005 green
>> > 21.5.2006 yellow
>> > 21.6.2006 blue
>> >
>> > how can I count all the green guys with the datum after 1.5.2005?
>> > countif will not probably work...
>> >

>>
>>
>>

8. ## Re: countif, sumproduct

or

=SUMPRODUCT(--(A1:A5>=--"2006-04-01"),-(A1:A5<=--"2006-05-02"),--(B1:B5="gre
en"))

--

HTH

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

<Andy B> wrote in message news:uWejjTlfFHA.3584@TK2MSFTNGP09.phx.gbl...
> Hi
>
> Try something like this:
>

=SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="gree
n"))
>
>
> --
> Andy.
>
>
> "mg" <mg@discussions.microsoft.com> wrote in message
> news:44CF5C68-B259-4BE8-B66D-C5CED941BA0D@microsoft.com...
> > what if i need to use a range in the date f.e. from 1.4.2005 to

2.5.2005?
> >
> > Bernard Liengme píse:
> >
> >> I will assume the first column are dates
> >> With dates in column A and colours in B
> >> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "mg" <mg@discussions.microsoft.com> wrote in message
> >> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
> >> > table:
> >> > 1.5.2006 blue
> >> > 2.5.2006 green
> >> > 30.4.2005 green
> >> > 21.5.2006 yellow
> >> > 21.6.2006 blue
> >> >
> >> > how can I count all the green guys with the datum after 1.5.2005?
> >> > countif will not probably work...
> >> >
> >>
> >>
> >>

>
>

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