Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
=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 |
#7
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
Complex formula help needed | Excel Worksheet Functions |