Need a formula to count the values for multiple values

Swetha

In Source sheet i have two columns Department and Grade. In Target sheet, i need the count for the departments with grades in different column. However department names starting with similar word need to be retrieved the count in the same cell.

Source Sheet:

Target Sheet: I need these values in the Target Sheet:

As of now, the formula which i used, i'm getting count for just HAL, BHEL & CITI... not for others that starts with similar word(CITI Test,HAL22 etc).

I need to add Contain in the formula. Not sure where to add in the formula.

Formula Used: =COUNTIFS({Department}, $[.]@row, {Grade}, [..]$1)

Swetha

@Andrée Starå : Could you please help on how to add Contain formula for @row?

Paul Newcome

You would use something like this:

=COUNTIFS({Department}, CONTAINS(Department@row, @cell), {Grade}, @cell = "A")

Swetha

@Paul Newcome : Thanks for the formula. It works!!! Incas-if we have two columns with departments and i have to use Countifs with Contain & OR. Could you please suggest the formula.

Incase, if data is not available in Department 1, it should look for Department 2 and retrieve the count for grades.

Need update for the formula =COUNTIFS({Department}, CONTAINS(Department@row, @cell), {Grade}, @cell = "A")

by looking it to second Department too.

Target sheet should retrieve:

Paul Newcome

@Swetha You would need to write a second COUNTIFS that looks at the second column and then add the two COUNTIFS together.

=COUNTIFS(.....................) + COUNTIFS(.................)

Nick Korna

Hi @Swetha,

This should do what you're after:

=COUNTIFS({Department 1}, CONTAINS(Department@row, @cell), {Grade}, "A") + COUNTIFS({Department 2}, CONTAINS(Department@row, @cell), {Grade}, "A") - COUNTIFS({Department 1}, CONTAINS(Department@row, @cell), {Department 2}, CONTAINS(Department@row, @cell), {Grade}, "A")

This is counting department 1 with the grade, department 2 with the grade, then subtracting the instances where the department is in both 1 & 2 (so it's not counted twice like the first instance).

Hope this is clear enough.

Swetha

@Nick Korna : Thanks it works!!!