complex formula

[Guest]

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!

 

[Frank Kabel]

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

 

[Guest]

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

 

[Domenic]

Try the following...

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

Hope this helps!

 

[Guest]

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

 

[Dave Peterson]

=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

 

[Guest]

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

Thank you - Happy Holidays!