Countif with multiple criteria
 

Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will
count the number of cells that meet two criteria--to count if there is a date
in one column AND if there is specific text in another column.

Column I contains dates (appearing as, for example, 7-Feb-08, but stored as
2/7/2008)
Column E contains text

I have been trying to find a function that would do something along the
lines of:
Count if column I contains a date (and is not blank) AND if column E
contains "GC"

Any suggestions would be greatly appreciated!

Countif with multiple criteria
 

You can only use COUNTIF for one criteria. For more than one you can use
this:

=SUMPRODUCT((I1:I1000)*(E1:E100="GC"))

Note that to Excel a date is just a number, and that a blank cell is
considered as being equivalent to zero in many formulae, so the first term
is checking for a number greater than zero in cells I1:I100. If you have
more rows than this then you have to change the range accordingly, but you
cannot use full-column references in versions before XL2007. If your dates
are fairly recent, then you could make the comparison 38000, as this is
equivalent to 14th January 2004.

Don't be put off by the name of the function - this will COUNT the cells
matching the criteria.

Hope this helps.

Pete

"girasole" wrote in message
...
Hi. I am trying to compose a function in an Excel 2003 spreadsheet that
will
count the number of cells that meet two criteria--to count if there is a
date
in one column AND if there is specific text in another column.

Column I contains dates (appearing as, for example, 7-Feb-08, but stored
as
2/7/2008)
Column E contains text

I have been trying to find a function that would do something along the
lines of:
Count if column I contains a date (and is not blank) AND if column E
contains "GC"

Any suggestions would be greatly appreciated!

Countif with multiple criteria
 

Check out Chip Pearson's site:
http://www.cpearson.com/excel/array.htm
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"girasole" wrote:

Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will
count the number of cells that meet two criteria--to count if there is a date
in one column AND if there is specific text in another column.

Column I contains dates (appearing as, for example, 7-Feb-08, but stored as
2/7/2008)
Column E contains text

I have been trying to find a function that would do something along the
lines of:
Count if column I contains a date (and is not blank) AND if column E
contains "GC"

Any suggestions would be greatly appreciated!

Countif with multiple criteria
 

This is great! Thanks!

"Gary Brown" wrote:

Check out Chip Pearson's site:
http://www.cpearson.com/excel/array.htm
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"girasole" wrote:

Hi. I am trying to compose a function in an Excel 2003 spreadsheet that will
count the number of cells that meet two criteria--to count if there is a date
in one column AND if there is specific text in another column.

Column I contains dates (appearing as, for example, 7-Feb-08, but stored as
2/7/2008)
Column E contains text

I have been trying to find a function that would do something along the
lines of:
Count if column I contains a date (and is not blank) AND if column E
contains "GC"

Any suggestions would be greatly appreciated!

Countif with multiple criteria
 

Thanks! I had tried using variations of the SUMPRODUCT function, but nothing
worked until your suggestion. Thanks again!

"Pete_UK" wrote:

You can only use COUNTIF for one criteria. For more than one you can use
this:

=SUMPRODUCT((I1:I1000)*(E1:E100="GC"))

Note that to Excel a date is just a number, and that a blank cell is
considered as being equivalent to zero in many formulae, so the first term
is checking for a number greater than zero in cells I1:I100. If you have
more rows than this then you have to change the range accordingly, but you
cannot use full-column references in versions before XL2007. If your dates
are fairly recent, then you could make the comparison 38000, as this is
equivalent to 14th January 2004.

Don't be put off by the name of the function - this will COUNT the cells
matching the criteria.

Hope this helps.

Pete

"girasole" wrote in message
...
Hi. I am trying to compose a function in an Excel 2003 spreadsheet that
will
count the number of cells that meet two criteria--to count if there is a
date
in one column AND if there is specific text in another column.

Column I contains dates (appearing as, for example, 7-Feb-08, but stored
as
2/7/2008)
Column E contains text

I have been trying to find a function that would do something along the
lines of:
Count if column I contains a date (and is not blank) AND if column E
contains "GC"

Any suggestions would be greatly appreciated!