Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
How would you write the formula when there are 3 columns involve?
A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
Just in March of 2006?
=sumproduct(--(text(a1:a10,"yyyymm")="200603"), --(b1:b10=8877),--(c1:c10="James")) Adjust the range to match, but you can't use the whole column. FPJ wrote: How would you write the formula when there are 3 columns involve? A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
Insert column A...copy your dates to read the month and year 2006-03. Insert
Column B...concatenate (D1,"-",C1) and run the formula to the end of your data...which will yield 8877-2006-03 (for James). Anywhere on your worksheet...or in a new worksheet, enter Countif(b1:b10,"8877-2006-03"). "Dave Peterson" wrote: Just in March of 2006? =sumproduct(--(text(a1:a10,"yyyymm")="200603"), --(b1:b10=8877),--(c1:c10="James")) Adjust the range to match, but you can't use the whole column. FPJ wrote: How would you write the formula when there are 3 columns involve? A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
I am getting a #VALUE! result. This may be due to some cells in column A have
the word VOID and some cells in columns B and C are blank. What will be the formula? Thanks again in advance. "Dave Peterson" wrote: Just in March of 2006? =sumproduct(--(text(a1:a10,"yyyymm")="200603"), --(b1:b10=8877),--(c1:c10="James")) Adjust the range to match, but you can't use the whole column. FPJ wrote: How would you write the formula when there are 3 columns involve? A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
I don't think it's the Void in your data in column A. Do you have any errors in
any of those ranges (#value!'s??). Also, make sure that each range has the same number of cells. If this doesn't help, post the formula you used. FPJ wrote: I am getting a #VALUE! result. This may be due to some cells in column A have the word VOID and some cells in columns B and C are blank. What will be the formula? Thanks again in advance. "Dave Peterson" wrote: Just in March of 2006? =sumproduct(--(text(a1:a10,"yyyymm")="200603"), --(b1:b10=8877),--(c1:c10="James")) Adjust the range to match, but you can't use the whole column. FPJ wrote: How would you write the formula when there are 3 columns involve? A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF or SUMPRODUCT formula
You're right. It was just my typo. The formula works just fine. Thanks a lot
again. Also, just want to mention, I added a $ sign for all the ranges so I can just copy and paste since I am working on a very large data (lots of cells to fill in with the formula). Again, Thank You. "Dave Peterson" wrote: I don't think it's the Void in your data in column A. Do you have any errors in any of those ranges (#value!'s??). Also, make sure that each range has the same number of cells. If this doesn't help, post the formula you used. FPJ wrote: I am getting a #VALUE! result. This may be due to some cells in column A have the word VOID and some cells in columns B and C are blank. What will be the formula? Thanks again in advance. "Dave Peterson" wrote: Just in March of 2006? =sumproduct(--(text(a1:a10,"yyyymm")="200603"), --(b1:b10=8877),--(c1:c10="James")) Adjust the range to match, but you can't use the whole column. FPJ wrote: How would you write the formula when there are 3 columns involve? A B C 2/3/2006 8877 Gerry 2/12/2006 8877 James 2/22/2006 5566 Cindy 3/1/2006 1122 Gerry 3/3/2006 0011 Gerry 3/11/2006 8877 James 3/11/2006 3344 Cindy 3/15/2006 8877 James 3/29/2006 1122 James I'd like to get the number of occurence of code 8877 (column B) by James (column C) that were made in month of March (column C). The result should be 2. Thanks in advance. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct, If, Sumif, Countif, Match?? | Excel Discussion (Misc queries) | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
I have a question regarding countif formula. | Excel Worksheet Functions | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) |