SOLVED

Formula to countif one column based on a word in another

Copper 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.

 

BACKGROUND:
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.

excellab01.png
excellab01chart.png

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

 

QUESTION:

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. 

excel.png

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 (Copper Contributor)
Solution
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")"
1 best response

Accepted Solutions
best response confirmed by misce (Copper Contributor)
Solution
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")"

View solution in original post