0

I want to count how many times a certain word appears in a column if it's in the same row --- if another word appears in another column.

I am working with 2 sheets.

I want to get the information in the 1st sheet, while looking into the 2nd sheet.

Column G has either deployed or pending

Column J has Department names

I want to get the number of times it says Deployed if it falls in the same row of a particular department name

I tried Various Countifs, but unsuccessful.

One countifs I tried was:

=COUNTIFS('Full List'!G:G, "deployed",'Full List'!J:J,"Operations")

Full List is the sheet name Operations is the department name

Any help will be appreciated

Improve this question
  • 1
    Please post some sample data. – Máté Juhász Oct 21 '16 at 4:46
  • 1
    =COUNTIFS('Full List'!G:G, "deployed",'Full List'!J:J,"Operations") As you say works perfectly for me, what seems to be the problem with it? – Christofer Weber Oct 21 '16 at 6:03
2

You can use Sumproduct instead the formula will be:
=SUMPRODUCT(--(G2:G14="Deployed")*(--(J2:J14="Operations")))
You can change G2:G14 and J2:J14 to your Data references but remember it should be the same number of rows in column G as column J in order to have a number result
And use -- to convert logical True to 1
When it is True in both column the formula will add 1 to the result
With countifs the formula is working:
=COUNTIFS(G2:G14,"deployed",J2:J14,"operations")
It gives the same result, check the references and the data
=COUNTIFS(Sheet1!G:G,"deployed",Sheet1!J:J,"operations")

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.