Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Nesting logicals within Countif command

Nesting logicals within Countif command

Nesting logicals within Countif command

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 !!


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)


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

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close