COUNTIF or SUMPRODUCT formula

[FPJ]

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]

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.

 

[GurlRossGurl]

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").

 

[FPJ]

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]

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]

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.