If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
=SUMPRODUCT(--(A2:A500="building1"),--(C2:C500="c"))
=SUMPRODUCT(--(A2:A500="building1"),--(D2500="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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help on Count function!!! | MJ | Worksheet Functions | 6 | May 17th, 2004 04:55 PM |
If/And function help | Milehigh | Worksheet Functions | 5 | January 30th, 2004 04:15 PM |
Referencing the cell that is calling a function. | Dutch | Worksheet Functions | 5 | January 26th, 2004 09:02 PM |