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
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
Learn all about them in Excel's built-in help, including some tutorials.
RE: Nesting logicals within Countif command
=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
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.