Combining SUMPRODUCT & COUNTIF with multiple criteria in sigle column

[drewberts]

I have a table which has a column containing job status (e.g. live, dead, invoiced etc) and a separate column with a job fee and a third column with a Sub Fee.

I have used:-

=SUMPRODUCT(SUMIF(TabJobs[Current Job Status],CHOOSE({1,2,3,4,5},Settings!$A$1,Settings!$A$2,Settings!$A$3,Settings!$A$4,Settings!$A$5),TabJobs[Sub Fee]))

which gives me a total value of all the jobs that have a sub fee which have one of 5 statuses listed on a sheet called Settings.

I want to do the same that returns the number of jobs.

I can see that simply substituting SUMIF for COUNTIF doesn't work as the criteria structure is different for COUNTIF but I can't work out the correct syntax.

Thanks

 

[RoryA]

For COUNTIF you'd remove the ,TabJobs[Sub Fee] part too.

 

[drewberts]

For COUNTIF you'd remove the ,TabJobs[Sub Fee] part too.

Thanks RoryA - I'd tried that but it counts the number in the jobs status column and not the sub fee column. Your suggestion leaves the formula with no reference to the sub fee column which is the one I want to count.

 

[RoryA]

Does the sub fee column match the criteria? I assume not, so am not really clear what you want to count exactly?

 

[drewberts]

My layout is:-

Current Job Status Fee Sub Fee
Raised 1000 100
Invoiced 2000 200
Raised 500 200
Programmed 4500 0
Dead 1500 100

The list of possible job statuses is on a sheet called Settings (theu are used for data validation from a dropdown list when pickeing Current Job Status). I've used the following to give me a sum for the sub fee column for all rows that contain any of the job statuses that are in cells A1 to A5 on the settings sheet.

=SUMPRODUCT(SUMIF(TabJobs[Current Job Status],CHOOSE({1,2,3,4,5},Settings!$A$1,Settings!$A$2,Settings!$A$3,Settings!$A$4,Settings!$A$5),TabJobs[Sub Fee]))

e.g. if the job statuses list was

Raised
On Hold
Requoted
Programmed
Invoiced
Shelved
Dead

then the sum returned would be £500

I want to do the do the same but count the instances in the Sub Fee column rather than sum them based on the same criteria, so the above example would return 3 (I need it to ignore any zero values in the count).

Hope that makes sense

 

[RoryA]

OK, the ignoring 0s was the key missing piece. You need:

=SUMPRODUCT(COUNTIFS(TabJobs[Current Job Status],CHOOSE({1,2,3,4,5},Settings!$A$1,Settings!$A$2,Settings!$A$3,Settings!$A$4,Settings!$A$5),TabJobs[Sub Fee],"<>0"))

 

[drewberts]

Brill thanks RoryA - this works in principle but the count is wrong - couln't work it out at first but it is also counting blanks. Can I exclude zeros AND blanks or does that give me too many criteria?

 

[RoryA]

Do you ever have any negative fees? If not, just change the last criterion to ">0"

 

[drewberts]

Perfect - all sorted. Thanks for your help

 

[RoryA]

You're welcome.