Nesting logicals within Countif command

[sean01]

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

 

[MintJulep]

Perfect job for a pivot table.

Learn all about them in Excel's built-in help, including some tutorials.

 

[joerd]

You can use an array formula (which may become quite slow in large spreadsheets):
[tt]=SUM($C$1:$C$3*($A$1:$A$3="supplier 1")*($B$1:$B$3="complete"))[/tt]
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:
[tt]=A1&B1[/tt] in cell D1 and so forth. Then, the sumif becomes:
[tt]=SUMIF($D$1:$D$3,"supplier 1complete",$C$1:$C$3)[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[EGT01]

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...
[tt]
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
[/tt]
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.