CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user9366125
user9366125

Reputation:

Excel Count and Multiply cells between sheets

I have 2 sheets, Sheet 1 and Sheet 2.

In Sheet 1 is a list of Vacancies in one cell and next to it is the Name of the person who has those Vacancies.

In Sheet 2 I am trying to find the TOTAL amount of Vacancies that each person listed has using a Formula

=COUNTIF(Sheet1!B:B,A2)

The only problem is that it needs to count in Sheet 1 how many vacancies a specific person has.

eg: it shows John has 3 vacancies when he should have 11.

enter image description here

enter image description here

Added a SUMIF attempt

enter image description here

Upvotes: 0

Views: 57

Answers (1)

JvdV
JvdV

Reputation: 75870

The issue is that you should use SUMIF() instead of COUNTIF() as per the comments above, though since you are not using Excel but Google Spreadsheets, this opens up other options (note, the two look alike but are actually very different).

You could try utilize QUERY():

enter image description here

Formula in D1:

=QUERY(A1:B,"Select B, Sum(A) where A is not null group by B label Sum(A) 'Total Vacancies'")

Upvotes: 1

Related Questions