Formula to countif one column based on a word in another

Occasional Contributor

Not sure if that title explains it very well! I've tried googling around and using nested IF/Countif functions but I've not had any luck so far.


I have a table, and I use it to create some pie-charts. As far as I could tell, I was unable to make pie-charts from my table directly as it is not numeric, so I created secondary tables (pictured below) with simple 'Countif' functions and made the charts from that numeric data.


This works fine for presenting data for the whole table, however, I also want to produce similar charts for each department.



I need a formula which essentially filters my original table by the 'College' column, and provides the numeric data for the outcome of how many 'Not applicable/In progress/complete' etc. 


So in layman's terms the formula should say IF column C = 'CLES', count if column F = 'Complete' 

And I'd repeat that for each department to create charts for each area.


The ultimate goal is to have these charts pulled into a SharePoint page, so it really needs to be fully automated (eg. no manual filtering)


Does that make sense? Is there a way to do this? Or alternatively am I massively overcomplicating the process? 


Thank you!

1 Reply
best response confirmed by misce (Occasional Contributor)
I'm silly - I tried just CountIfs at the start with no luck, but I went back to it and it worked this time, so I must have just got the syntax or something wrong first time round.

To confirm, the formula I was looking for was:
"=COUNTIFS(Table1[LAB01], "Not applicable", Table1[College], "CLES")"