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...

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

"mg" wrote in message
...
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...

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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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...

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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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
View this thread: http://www.excelforum.com/showthread...hreadid=383850

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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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...

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" wrote
in message ...

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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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
View this thread: http://www.excelforum.com/showthread...hreadid=383850

Hi

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


--
Andy.


"mg" wrote in message
...
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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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...

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 ...
Hi

Try something like this:

=SUMPRODUCT(--(A1:A5=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="gree
n"))


--
Andy.


"mg" wrote in message
...
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:A5DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mg" wrote in message
...
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...