# Combining SUMPRODUCT & COUNTIF with multiple criteria in sigle column

#### drewberts

##### Board Regular
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### RoryA

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

#### drewberts

##### Board Regular
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

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

Last edited:

#### drewberts

##### Board Regular
My layout is:-

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

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

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

##### MrExcel MVP, Moderator
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

##### Board Regular
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

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

Last edited:

#### drewberts

##### Board Regular
Perfect - all sorted. Thanks for your help

You're welcome.