Countif with multiple criteria

[girasole]

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!

 

[Pete_UK]

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

=SUMPRODUCT((I1:I100>0)*(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

 

[Gary Brown]

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]

This is great! Thanks!

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]

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