|
functions
In column A are dates
In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn |
Hi,
Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
I do not want to sum the 1,2,3. I want to count each one as 1, the answer
would be 3. "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04
a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
It appears that you've switched your dates from ColumnA to ColumnB in your
two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Hi,
try this... =countif(a1:a3,"" & a4) peter -----Original Message----- I do not want to sum the 1,2,3. I want to count each one as 1, the answer would be 3. "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . . |
Hi,
sorry, I didn't see your other post. see if this is what you need... =SUM(IF( ( $a$1:$a$3$a$4)* ($b$1:$b$3=$b$4 ) ,1,0)) a4 has a date criteria b4 has a criteria that must match items in b peter -----Original Message----- I do not want to sum the 1,2,3. I want to count each one as 1, the answer would be 3. "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . . |
Actually this is what worked:
=SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Ok, one more question. Take the same formula and instead of AF3 (which is a
date) I need between AF2 and AF3. (AF2 & <AF3 Thanks Dawn "Dawn S" wrote: Actually this is what worked: =SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Just *include* the other argument into the formula:
=SUMPRODUCT(--(A2:A999AF2),--(A2:A999<AF3),--(B2:B999=AG2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Ok, one more question. Take the same formula and instead of AF3 (which is a date) I need between AF2 and AF3. (AF2 & <AF3 Thanks Dawn "Dawn S" wrote: Actually this is what worked: =SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Thanks for the help, I figured it out 3 minutes before your response.
"RagDyeR" wrote: Just *include* the other argument into the formula: =SUMPRODUCT(--(A2:A999AF2),--(A2:A999<AF3),--(B2:B999=AG2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Ok, one more question. Take the same formula and instead of AF3 (which is a date) I need between AF2 and AF3. (AF2 & <AF3 Thanks Dawn "Dawn S" wrote: Actually this is what worked: =SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
That's one of the beauties of this system..........."explaining your problem
so others can understand it enough to help you, often shows you the way to get the answer yourself........" Vaya con Dios, Chuck, CABGx3 "Dawn S" wrote in message ... Thanks for the help, I figured it out 3 minutes before your response. "RagDyeR" wrote: Just *include* the other argument into the formula: =SUMPRODUCT(--(A2:A999AF2),--(A2:A999<AF3),--(B2:B999=AG2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Ok, one more question. Take the same formula and instead of AF3 (which is a date) I need between AF2 and AF3. (AF2 & <AF3 Thanks Dawn "Dawn S" wrote: Actually this is what worked: =SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
Thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dawn S" wrote in message ... Thanks for the help, I figured it out 3 minutes before your response. "RagDyeR" wrote: Just *include* the other argument into the formula: =SUMPRODUCT(--(A2:A999AF2),--(A2:A999<AF3),--(B2:B999=AG2)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Ok, one more question. Take the same formula and instead of AF3 (which is a date) I need between AF2 and AF3. (AF2 & <AF3 Thanks Dawn "Dawn S" wrote: Actually this is what worked: =SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2)) Thanks for you help "RagDyeR" wrote: It appears that you've switched your dates from ColumnA to ColumnB in your two examples. Anyway, try this: Dates in ColumnA, from A3 to A100. Values in ColumnB, from B3 to B100 Column headers in A2 and B2. Enter the specific start date in A1, And the value to find (count) in B1. Then, try this: =SUMPRODUCT((A3:A100A1)*(B3:B100=B1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Dawn S" wrote in message ... Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04 a b 1 12/30/04 1 11/15/04 1 12/15/04 2 12/1/04 With this my answer would be 2 "peter" wrote: Hi, Try this... a b 1 1/1/2005 1 2 1/3/2005 2 3 1/6/2005 3 4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will return a 6 make sure to use the cntl shift enter combination after writing the formula. peter -----Original Message----- In column A are dates In column B are values (1, 2, 3) I want to look at A for a date greater than a specific date then match to B for a specific number and count as 1 if they match if A 11/30/04 and B = 1 then count as 1 If I have 5 rows that fit the criteria then the answer would be 5. Please help with the formula, I have tried countif and nested in countif. Thanks Dawn . |
| All times are GMT +1. The time now is 08:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com