Combining SUMPRODUCT & COUNTIF with multiple criteria in sigle column

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
145
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
 

Some videos you may like

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
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For COUNTIF you'd remove the ,TabJobs[Sub Fee] part too.
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
145
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
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Sep 5, 2013
Messages
145
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Sep 5, 2013
Messages
145
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
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Do you ever have any negative fees? If not, just change the last criterion to ">0"
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You're welcome. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,452
Messages
5,414,611
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top