|
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... |
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... |
| All times are GMT +1. The time now is 08:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com