"count if" function based on value of another cell
 

Hello, (Excel 2003)
Column A contains list of buildings (25+ building names, 1000+ rows)
Column D contains a letter indicating if work done ("c")
Column E contains text indicating work not done ("n/c")

I would like to count the "c"'s grouped by Building name and in a separate
cell count the "n/c"'s grouped by Building name. I have tried VLookup with
CountIf without any luck.
--
Thank-you and all suggestions are appreciated.

You can use sumproduct
check out the link below
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


-----Original Message-----
Hello, (Excel 2003)
Column A contains list of buildings (25+ building names,
1000+ rows)
Column D contains a letter indicating if work done ("c")
Column E contains text indicating work not done ("n/c")

I would like to count the "c"'s grouped by Building name
and in a separate
cell count the "n/c"'s grouped by Building name. I have
tried VLookup with
CountIf without any luck.
--
Thank-you and all suggestions are appreciated.
.

=SUMPRODUCT(--(A2:A500="building1"),--(C2:C500="c"))

=SUMPRODUCT(--(A2:A500="building1"),--(D2:D500="n/c"))

--

Regards,

Peo Sjoblom

"Anauna" wrote in message
...
Hello, (Excel 2003)
Column A contains list of buildings (25+ building names, 1000+ rows)
Column D contains a letter indicating if work done ("c")
Column E contains text indicating work not done ("n/c")

I would like to count the "c"'s grouped by Building name and in a separate
cell count the "n/c"'s grouped by Building name. I have tried VLookup
with
CountIf without any luck.
--
Thank-you and all suggestions are appreciated.

Anauna,

Select your data table, then use Data Pivot Table. Drag the header from
column A to the row fields area, and your headers from Columns D and E to
the data field areas. You should then get a count of each for each
building.

HTH,
Bernie
MS Excel MVP

"Anauna" wrote in message
...
Hello, (Excel 2003)
Column A contains list of buildings (25+ building names, 1000+ rows)
Column D contains a letter indicating if work done ("c")
Column E contains text indicating work not done ("n/c")

I would like to count the "c"'s grouped by Building name and in a separate
cell count the "n/c"'s grouped by Building name. I have tried VLookup
with
CountIf without any luck.
--
Thank-you and all suggestions are appreciated.