complex formula
 

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

Hi
if you also want to count values which only have for example 'one' as part
of the first column (e.g. 'this is one') then use
=SUMPRODUCT(--ISNUMBER(SEARCH("one",A1:A100)),--ISNUMBER(SEARCH("two",B1:B100)),--(C1:C100<DATE(2004,12,1)))

If one or two have to be the complete entry of the cell use
=SUMPRODUCT(--(A1:A100="one"),--(B1:B100="two"),--(C1:C100<DATE(2004,12,1)))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany
"shmurphing" schrieb im Newsbeitrag
...
Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then
count
the cells the ALSO are less than a specific date. The count at the end
will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

One way

=SUMPRODUCT(--(A2:A200="One"),--(B2:B200="Two"),--(C2:C200<DATE(2004,12,1)))


this assumes that One and Two are alone in their cells and not part of a
larger text string and that the dates in C (or whatever column holds the
dates) are real dates and not text

Regards,

Peo Sjoblom

"shmurphing" wrote:

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

Try the following...

=SUMPRODUCT(--(A1:A10="one"),--(B1:B10="two"),--(C1:C10<DATE(2004,12,1)))

Hope this helps!

In article ,
"shmurphing" wrote:

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

I like this formula, but I have never seen the "--" notation before. What is
this used for? Somehow it changes the formula to a numeric output? Please
explain.. for these curious people!

Thanks,
Patrick G

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A200="One"),--(B2:B200="Two"),--(C2:C200<DATE(2004,12,1)))


this assumes that One and Two are alone in their cells and not part of a
larger text string and that the dates in C (or whatever column holds the
dates) are real dates and not text

Regards,

Peo Sjoblom

"shmurphing" wrote:

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

=Sumproduct()
likes to work with numbers.

a2:A200="one"
will be 198 true/falses.

the rightmost minus (in the double minus) will convert true/falses to -1/0's,
then the leftmost will change the -1 to +1.

J.E. McGimpsey has notes at:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html




Patrick G wrote:

I like this formula, but I have never seen the "--" notation before. What is
this used for? Somehow it changes the formula to a numeric output? Please
explain.. for these curious people!

Thanks,
Patrick G

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A200="One"),--(B2:B200="Two"),--(C2:C200<DATE(2004,12,1)))


this assumes that One and Two are alone in their cells and not part of a
larger text string and that the dates in C (or whatever column holds the
dates) are real dates and not text

Regards,

Peo Sjoblom

"shmurphing" wrote:

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!

--

Dave Peterson

Wow! This works great. Think I should be able to take care of the rest of
the spreadsheet with ease.

Thank you - Happy Holidays!

"shmurphing" wrote:

Can anyone tell me how to performt he following?

I want to count the cells in column A that contain the word "One" and then
count the cells in column B that ALSO contain the word "Two" and then count
the cells the ALSO are less than a specific date. The count at the end will
indicate that the row had one, two, and was less than dec 1, 2004.

Help!