Count Question

[John]

I have a spreadsheet with over 6,000 rows of data. Column A lists a
category of which there are 8 possibilities. I know how to use =countif
to determine how many times a certain category appears. However, beside
column A is a column with dates. These dates indicate when a certain
entry was closed. If there is no date in column B then the entry, or
case, is still considered open. I need to have a count of each category
for the cases that are still open. I assume a nested formula is the
key, but I'm not clear on what that would be. Any help would be much
appreciated.

Example:

Column A Column B
Row 1 AAA 7/25/04
Row 2 BBB
Row 3 BBB 8/1/04
Row 4 AAA 9/1/04

In the example above, I need a formula that would tell me how many times
BBB appears without a date beside it.

Thanks so much,

John

 

[DDM]

John, a formula like this one will give you the count you're looking for:

=SUMPRODUCT(($A$1:$A$6000="BBB")*($B$1:$B$6000=""))

Substitute your ranges for the ranges above. And, of course, you can list
the 8 categories in a column and substitute the cell reference for the
category value "BBB". Then you can copy the formula down and get counts for
each category.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A4="BBB"),--ISBLANK(B1:B4))

 

[John]

DDM & JE.... thank you very much for your kind assistance!!!