"count if" function based on value of another cell

A

Anauna

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.
 
P

Peo Sjoblom

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

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

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Number of Unique Cells That Don't Have Certain Characters 0
countifs Function not giving desired result with date criteria 2
Counting cells with SUMPRODUCT and conditionals...? 1
Get sum of values in a cell based on unique values in another cell 3
Count nonblank cells based on another column 1
combine three if functions 3
trouble with (complicated) vlookup searching another workbook 2
Increment a number based on four adjoing cells 2

Top