×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Nesting logicals within Countif command

## Nesting logicals within Countif command

(OP)
Help !!

I am trying to construct a spreadsheet that tracks whether work is completed or in progress against a number of suppliers - the data is similar to that below

A              B                   C
1     supplier 1     complete            $1000 2 supplier 2 complete$300
3     supplier 1     work in progress    $400 etc etc What I would like to do is to be display all the jobs supplier 1 has (simple - use the countif function I can also display the total and average spend using the SUMIF function - no problem there. My problem is that I can't seem to construct the formula for counting the amount of work supplier one has completed........... Any ideas ? Its driving me crazy !! Sean ### RE: Nesting logicals within Countif command Perfect job for a pivot table. Learn all about them in Excel's built-in help, including some tutorials. ### RE: Nesting logicals within Countif command You can use an array formula (which may become quite slow in large spreadsheets): =SUM($C$1:$C$3*($A$1:$A$3="supplier 1")*($B$1:$B$3="complete")) Remember to press Ctrl-enter after entering the formula to tell Excel that this is an array formula. The other option is to construct a separate column where you concatenate the data in column A and B: =A1&B1 in cell D1 and so forth. Then, the sumif becomes: =SUMIF($D$1:$D$3,"supplier 1complete",$C$1:$C$3) Cheers, Joerd Please see FAQ731-376 for tips on how to make the best use of Eng-Tips. ### RE: Nesting logicals within Countif command Could also try using one of the database functions such as DCOUNT but I think it will require you to have column names in the first row. For example... A B C 1 Supplier Status Cost 2 supplier 1 complete$1,000
3 supplier 2  complete             $300 4 supplier 1 work in progress$400
6
7
8 Supplier    Status              Total=DCOUNT(A1:C4,C1,A8:B9)
9 supplier 1  complete               1

In the DCOUNT function
A1:C4 = database range, including column names
C1 = column name to count numbers, DCOUNT only counts numbers
A8:B9 = criteria range, row 8 = the column names to search, row 9 = criteria to check under the column name.
Logical operators are allowed in the criteria so you can search <>supplier 1 = "not supplier 1"
I like DCOUNT because you can setup a criteria range which is easy to change to get different info and see the criteria.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!